Access SQL: dateValue

option explicit

sub dateValueTest() ' {

   dim db as dao.database
   set db =  application.currentDB

 '
 ' Drop table if it already exists:
 '
   if not isNull(dLookup("Name", "MSysObjects", "Name='dateValueTest'")) then db.execute("drop table dateValueTest")


 '
 ' Create test table …
 '
   db.execute("create table dateValueTest(d date, t varchar(19))")

 '
 ' … and fill some values in it:
 '
   db.execute("insert into dateValueTest values (#2012-12-12 12:12:12#, '2012-12-12 12:12:12')")
   db.execute("insert into dateValueTest values (#2001-01-01 00:00:00#, '2001-01-01 00:00:00')")
   db.execute("insert into dateValueTest values (#2023-12-31 23:59:59#, '2023-12-31 23-59:59')")
 '
 ' Using dateValue will not work on null values:
 '
 ' db.execute("insert into dateValueTest values ( null                , 'null'               )")


 '
 ' Use dateValue(…) to get only the date without time.
 '
   dim rs as dao.recordSet
   set rs = db.openRecordset("select dateValue(d) as dt, t from dateValueTest")
   do while not rs.eof ' {
      debug.print (rs!dt & " | " & rs!t)
      rs.moveNext
   loop

end sub ' }

12.12.2012 | 2012-12-12 12:12:12
01.01.2001 | 2001-01-01 00:00:00
31.12.2023 | 2023-12-31 23-59:59