Insert date-time value using SQL?

[Jacob Havkrog]

Hi group.

How do I specify a date-time value MS Access database SQL query.

I know, that to specify a date value, I need the syntax
SELECT * FROM Ordrer WHERE Mydate= #5/10/96#

But what if I need to specify a date time value, like "2006-8-31 16:27:06"?

Is there a precision problem here? I have a database field of type DATETIME
where I store time stamps, and I need to select fields with a certain time
stamp.

Thanks for your help.

Regards

Jacob

 

[Douglas J. Steele]

Access should correctly interpret #2006-8-31 16:27:06#. However, since
date/times are stored as 8 byte floating point numbers (the integer portion
represents the date as the number of days relative to 30 Dec, 1899, while
the decimal portion represents the time as a fraction of a day), there can
be the usual problems with floating point round-off errors.

If you're prompting the user to input a date and time, you could consider
something like:

WHERE MyDateTime BETWEEN DateAdd("s", -1, [Input a Date and Time]) AND
DateAdd("s", 1, [Input a Date and Time])

That will look for everything 1 second on either side of the date/time
input.

 

[Pieter Wijnen]

Same way
SELECT * FROM Ordrer WHERE Mydate=#2006-05-10 16:27:06#
Note that I used the Military format
SELECT * FROM Ordrer WHERE Mydate=#05/10/06 16:27:06#
yields the same result though

Pieter