• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

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
0
ray14
Asked:
ray14
  • 3
  • 3
  • 2
  • +5
1 Solution
 
wolfroseCommented:
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.
0
 
EgoreCommented:
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.
0
 
JH0401Commented:
Try separating into 2 fields.  One for date & one for time!
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
trkcorpCommented:
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.
0
 
ray14Author Commented:
Sirs ,

I use this SQL in VB6, then is it the same as you mentioned?
0
 
trkcorpCommented:
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'"
0
 
supunrCommented:
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!
0
 
trkcorpCommented:
You don't have to use # around date fields in access 2000.
0
 
Arthur_WoodCommented:
or you can use the Between Operator:

Select * from Table1 where DDate BETWEEN Date() and Date() + .9999

Since Date() ios equivalent to the Date AT MIDNIGHT, and Date() + .99999 would get you to a about 1 second before midnight, that would just about cover the ENTIRE day.

AW
0
 
supunrCommented:
SSql = "Select * from Table1 where Format(DDate, ""YYYY-MMM-DD"") = " & Format(Date(), "YYYY-MMM-DD")
0
 
supunrCommented:
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.
0
 
manduz_griffusCommented:
Try:

SQLQuery = "Select * from Table1 WHERE DDate >= #" & FormatDateTime(DateValue(Now) & "12:00 AM", vbGeneralDate) & "# AND DDate <= #" & FormatDateTime(DateValue(Now) & "11:59:59 PM", vbGeneralDate) & "#"
0
 
EgoreCommented:
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...
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now