Easy: How to put a date literal in an SQL query?

[Rufus DeDufus]

I have a table with multiple records containing uninitialized DATE/TIME
fields, displayed as 12:00:00 AM.

I want to query for them, viz:

SELECT * FROM MYTABLE where MYDATETIME = 12:00:00 am.

or,

SELECT * FROM MYTABLE where MYDATETIME = 1:23:45 pm.

But, of course, the syntax for the date and time field is wrong.

How do I do this? This is an elementary matter of syntax.

Using Access 2000.

Thanks.

Eric

 

[Duane Hookom]

Try:
WHERE MyDateTime = 0
This assumes you are actually seeing 12:00 AM in the field rather than no
value.

 

[John Vinson]

I have a table with multiple records containing uninitialized DATE/TIME
fields, displayed as 12:00:00 AM.

I want to query for them, viz:

SELECT * FROM MYTABLE where MYDATETIME = 12:00:00 am.

or,

SELECT * FROM MYTABLE where MYDATETIME = 1:23:45 pm.

But, of course, the syntax for the date and time field is wrong.

How do I do this? This is an elementary matter of syntax.

Delimit the date/time field with #:

WHERE MyDateTime = #1:23:45 pm#

Note that this is a bit iffy: date/times are stored internally as a
Double Float number, a count of days and fractions of a day since
midnight, December 30, 1899. Depending on how the date field gets
filled, it might be a few microseconds off and would not match. Safer
(but less efficient, since it will prevent the use of indexes) would
be to convert the date/time to a String and compare strings:

SELECT * FROM mytable
WHERE Format(mytable.mydatetime, "hh:nn:ss ampm") = "01:23:45 am"

 

[Tim Ferguson]

I have a table with multiple records containing uninitialized DATE/TIME
fields, displayed as 12:00:00 AM.

I want to query for them, viz:

If you really mean they are uninitialised as in NULL, then you need a
criterion to look for null values:

SELECT Something FROM MyTable
WHERE MyDateTime IS NULL;

If you know there is a value, but you want midnight values only, then use
the TIMEVALUE function to discard the date parts:

SELECT Something FROM MyTable
WHERE TIMEVALUE(MyDateTime) = 0;

Or
SELECT Something FROM MyTable
WHERE TIMEVALUE(MyDateTime) = #00:00#;


Hope that helps


Tim F