By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,609 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,609 IT Pros & Developers. It's quick & easy.

Compare dates in Access SQL?

P: 19
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!
Nov 29 '06 #1
Share this Question
Share on Google+
8 Replies


Expert 5K+
P: 8,434
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?
Nov 30 '06 #2

sashi
Expert 100+
P: 1,754
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.

Expand|Select|Wrap|Line Numbers
  1.   "SELECT * FROM Plan WHERE #Date1# > #Date2#"
  2.  
Nov 30 '06 #3

P: 19
That should work just fine. They are fields of the type "Date/Time", correct?
Exactly.. gonna try that!
Nov 30 '06 #4

Expert 5K+
P: 8,434
Killer was correct, you may want to try the below code segment too, hope it helps. Good luck & Take care.
Expand|Select|Wrap|Line Numbers
  1.   "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...
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Plan WHERE TextField = 'Some String'
  2. SELECT * FROM Plan WHERE DateField > #11/30/2006#
Nov 30 '06 #5

atsukoarai86
P: 6
Hi there,

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

Expand|Select|Wrap|Line Numbers
  1.   "SELECT * FROM Plan WHERE #Date1# > #Date2#"
  2.  
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.
Sep 18 '08 #6

P: 3
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...
Oct 7 '09 #7

smartchap
100+
P: 236
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.
Oct 9 '09 #8

Dököll
Expert 100+
P: 2,364
@RobinDiederen
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:-)
Oct 10 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.