Easy Date Question

[NateUNI]

Im trying to do a easy query,

SELECT BugTracking.[Reported Date]
FROM BugTracking
WHERE (((BugTracking.[Reported Date]) Like #12/15/2003#));

which returns 0 records, however the Reported Date field in the database is a type date/time so if i do this query:

SELECT BugTracking.[Reported Date]
FROM BugTracking
WHERE (((BugTracking.[Reported Date]) Like #12/15/2003 2:55:25 PM#));

it returns a record. How do I change my first query so that i can only pass it a date and it will find all dates the match it? Thanks!!

 

[swaybright]

Change all # to * and that should do it.

Shane

 

[dhookom]

SELECT DateValue([Reported Date]) as ReportedDate
FROM BugTracking
WHERE [Reported Date] = #12/15/2003#;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NateUNI]

I tried this,

SELECT DateValue([Reported Date]) AS ReportedDate, BugTracking.[Tracking Number]
FROM BugTracking
WHERE (((BugTracking.[Reported Date]) = #12/15/2003#));

and it does not bring back any rows, any other ideas, Thanks!!

 

[r937]

the problem seems to be that the stored datetime values include a time component, whereas when you compare to a literal like #12/15/2003# which has no time component therefore it defaults to 00:00 and does not match

so try this:

[tt]WHERE format(BugTracking.[Reported Date]
,"mm/dd/yyyy&quot = '12/15/2003'[/tt]

note that FORMAT() produces a character string, so it is compared to '12/15/2003' not #12/15/2003#

rudy

http://r937.com/

 

[dhookom]

Sorry, the sql should have been:
SELECT [Reported Date] , [Tracking Number]
FROM BugTracking
WHERE DateValue([Reported Date]) = #12/15/2003#;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.