Compare dates in Access SQL?

[RobinDiederen]

Suppose, I want to compare 2 dates in an SQL query, running from Access (from VB code) like this:

"SELECT * FROM Plan WHERE Date1 > Date2"

Will that work? Thanks in advance!

[Killer42]

Suppose, I want to compare 2 dates in an SQL query, running from Access (from VB code) like this:
"SELECT * FROM Plan WHERE Date1 > Date2"
Will that work? Thanks in advance!

That should work just fine. They are fields of the type "Date/Time", correct?

[sashi]

Suppose, I want to compare 2 dates in an SQL query, running from Access (from VB code) like this:

"SELECT * FROM Plan WHERE Date1 > Date2"

Will that work? Thanks in advance!

Hi there,

Killer was correct, you may want to try the below code segment too, hope it helps. Good luck & Take care.

  "SELECT * FROM Plan WHERE #Date1# > #Date2#"

[RobinDiederen]

That should work just fine. They are fields of the type "Date/Time", correct?

Exactly.. gonna try that!

[Killer42]

Killer was correct, you may want to try the below code segment too, hope it helps. Good luck & Take care.

  "SELECT * FROM Plan WHERE #Date1# > #Date2#"

Good point. I was assuming that Date1 and Date2 actually both referred to database fields. To specify a date literal, you need to surround it with hashes (#) the same way you do with quotes around a string. To illustrate...

SELECT * FROM Plan WHERE TextField = 'Some String'
SELECT * FROM Plan WHERE DateField > #11/30/2006#

[atsukoarai86]

Hi there,

Killer was correct, you may want to try the below code segment too, hope it helps. Good luck & Take care.

  "SELECT * FROM Plan WHERE #Date1# > #Date2#"

lol this post actually helps me too. I forgot...well, actually I didn't know that you needed to put the date in # #s ....so my WHERE statement wasn't executing and I couldn't understand why!!!

boy I love forums. ^_^ Thank you.

[mikkaro04]

Follow Up question

hi.. i have a problem almost the same as the one posted so i decided to post it as a reply.. hope someone could help me..
i want to compare dates from access to a date from DTpicker control in VB 6.0.. this is my code but it doesnt work coz i get a syntax error..

"Select * from Reservations where DateCheckIn <= #date1# and DateCheckOut > #date1# and [RoomNo] = ' " & cmb_room & " ' "

"Select * from Reservations where #DateCheckIn# <= #date1# and #DateCheckOut# > #date1# and [RoomNo] = ' " & cmb_room & " ' "

notes:
DateCheckIn and DateCheckOut are access fields with Data Type "Date/Time"
date1 and date2 are Values from my two different DTpickers.
Reservations is my table name

both codes give me a syntax error.. can anyone please suggest a better code?

thnx a lot...

[smartchap]

Please see other post where u posted same question. Plz visit the following link for correction of query:

http://www.vbcity.com/forums/topic.asp?tid=32158

Correct the following query:

"Select * from Reservations where DateCheckIn <= #date1# and DateCheckOut > #date1# and [RoomNo] = ' " & cmb_room & " ' "

As:

"Select * from Reservations where DateCheckIn <= #" & date1 & "# and DateCheckOut > #" & date1 & "# and [RoomNo] = ' " & cmb_room & " ' "


Similarly correct query:

"Select * from Reservations where #DateCheckIn# <= #date1# and #DateCheckOut# > #date1# and [RoomNo] = ' " & cmb_room & " ' "

As:

"Select * from Reservations where #DateCheckIn# <= #" & date1 & "# and #DateCheckOut# > #" & date1 & "# and [RoomNo] = ' " & cmb_room & " ' "

Hope it works perfect.

[Dököll]

Suppose, I want to compare 2 dates in an SQL query, running from Access (from VB code) like this:

"SELECT * FROM Plan WHERE Date1 > Date2"

Will that work? Thanks in advance!

It is so funny you wrote, I am working with dates today-)

It looks like it should work. You just need to be careful with database columns as far as dates types, Short Date, Long Date and so on. In which case you should try to go ahead and convert whatever date you are selecting from (a vb form), you will aways be sure you are sending the right dates over.

Don't go through what I went through:


...

WHERE Convert(varchar (12), YourDate, 101) = '"+YourDate+ "')

above should let you search/select using mm/dd/yy format, even though the database date is mm/dd/yyyy hh ss.. this that and the other.

Good luck, do stay tuned for a better response if this is not it:-)