?
Solved

Date in SQL

Posted on 2003-03-12
13
Medium Priority
?
180 Views
Last Modified: 2010-05-01
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
Comment
Question by:ray14
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +5
13 Comments
 
LVL 1

Expert Comment

by:wolfrose
ID: 8122528
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
 
LVL 3

Expert Comment

by:Egore
ID: 8122645
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
 
LVL 1

Expert Comment

by:JH0401
ID: 8122853
Try separating into 2 fields.  One for date & one for time!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:trkcorp
ID: 8122855
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
 

Author Comment

by:ray14
ID: 8123164
Sirs ,

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

Expert Comment

by:trkcorp
ID: 8123248
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
 
LVL 11

Expert Comment

by:supunr
ID: 8123302
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
 
LVL 4

Expert Comment

by:trkcorp
ID: 8123388
You don't have to use # around date fields in access 2000.
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 200 total points
ID: 8124503
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
 
LVL 11

Expert Comment

by:supunr
ID: 8124636
SSql = "Select * from Table1 where Format(DDate, ""YYYY-MMM-DD"") = " & Format(Date(), "YYYY-MMM-DD")
0
 
LVL 11

Expert Comment

by:supunr
ID: 8124639
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
 

Expert Comment

by:manduz_griffus
ID: 8125242
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
 
LVL 3

Expert Comment

by:Egore
ID: 8128226
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 2 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question