asked on

Date in SQL

in my Access 2000 mdb , Table1 contains a Date/Time field called "DDate" and its format is General (I mean it should put the date and the time as well)
in the following SQL I try to filter data which the date of DDate is today , But It gives me nothing
SSql = "Select * from Table1 where DDate = Date()"
Please ammend it.

Thanx

wolfrose

I ran into a problem similar to that one myself. The DBA put the date's format as text. I had to use a for-next loop to look at each record and sort out the ones I wanted. It didn't take long, just more typing.

Egore

This is a bit of a hack, but you could do:

Select * from Table1 where DDate BETWEEN Date() + ' 00:00:00' AND Date() + '23:59:59'

That's the syntax for SQL Server, but I think it's the same in Access.

JH0401

Try separating into 2 fields. One for date & one for time!

trkcorp

Your date contains time. If it did not, your statement would work. Eqore was close but here is the actual syntax for ACCESS:

Select * from Table1 where DDate >= Date() & " 00:00:00" AND DDate <= Date() & " 23:59:59"

OR like Eqore said:

Select * from Table1 where DDate BETWEEN Date() & " 00:00:00" AND Date() & " 23:59:59"

Be sure and include the space ("^00:00:00") ^=Space on the time or you will get a datatype error because access won't be able to interpret properly.

ray14

ASKER

Sirs ,

I use this SQL in VB6, then is it the same as you mentioned?

trkcorp

Try this:
SQL = "Select * from Table1 where DDate >= '" & Date & " 00:00:00' and DDate <= '" & Date & " 23:59:59'"

OR like Eqore said:

SQL = "Select * from Table1 where DDate BETWEEN '" & Date & " 00:00:00' And '" & Date & " 23:59:59'"

supunr

use this format...

SSql = "Select * from Table1 where DDate = #" & Format(Date(), "YYYY-MMM-DD") & "#"

You have to use # around date fields.

Also format the date to MMM (eg. Jan, Feb) format because access I think accept the American format (MM-DD-YYYY). so, if the date is 2nd Jan 2003, it will be 1-2-2003 for SQL, but if you format it as 2-Jan-2003, then there is not a problem.

(Sorry about all this american standard thing. I live/work in Aus and we use DD-MM-YYYY format and I have to always use this MMM format to get the confution out.)

Good Luck!

trkcorp

You don't have to use # around date fields in access 2000.

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

supunr

SSql = "Select * from Table1 where Format(DDate, ""YYYY-MMM-DD"") = " & Format(Date(), "YYYY-MMM-DD")

supunr

or

SSql = "Select * from Table1 where Format(DDate, ""YYYY-MMM-DD"") = #" & Format(Date(), "YYYY-MMM-DD") & "#"

not sure which of the above 2 will work.

manduz_griffus

Try:

SQLQuery = "Select * from Table1 WHERE DDate >= #" & FormatDateTime(DateValue(Now) & "12:00 AM", vbGeneralDate) & "# AND DDate <= #" & FormatDateTime(DateValue(Now) & "11:59:59 PM", vbGeneralDate) & "#"

Egore

I pity ray14's decision on who to give the points to... That's going to be a doozy! :)

I cast my vote for supunr's:

SSql = "Select * from Table1 where Format(DDate, ""YYYY-MMM-DD"") = " & Format(Date(), "YYYY-MMM-DD")

That's seems to be the cleanest solution...