SELECT ....... WHERE date = GetDate()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT ....... WHERE date = GetDate()

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-11-10 : 15:08:27

I'm having a problem doing a query that returns all rows where a date time field is equal to today's date.

Do I have to do some sort of conversion on GetDate() or on the field I'm checking in order for the comparison to work?

Example:


select * 
from   store
where  deliveryDate = GetDate()          


sample data:

orderID               deliveryDate:           
  1            11/10/2008 12:00:00 AM
  2            11/10/2008 12:00:00 AM
  3            11/10/2008 12:00:00 AM
  4            11/11/2008 12:00:00 AM
  5            11/11/2008 12:00:00 AM


Rows w/ an orderID of 1, 2, and 3 should be returned because they have today's date.

Thanks in advance for any help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 15:10:27

select top 1 with ties *
from store
where DATEDIFF(DAY, deliveryDate, GetDate() ) = 0

E 12°55'05.63"
N 56°04'39.26"

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-11-10 : 15:12:24

That is because getdate() has hrs, min, sec

select *
from store
where convert(varchar, deliveryDate, 112) = convert(varchar, GetDate(), 112)

should work too

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-11-10 : 15:17:22

Neither of these worked, I'm getting 0 rows and I for sure have orders w/ a delivery date of today in my db.

I think the time doesn't even matter in the comparison. When the values are entered into the database, only the date (11/10/2008) is entered. 12:00:00 AM is entered automatically as the default time. Can this be creating the issue?

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-11-10 : 15:18:29

Never mind. Thanks guys, I got the second solution to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 23:11:58

quote:
Originally posted by crugerenator

Never mind. Thanks guys, I got the second solution to work.

First solution will also work provide you're using sql 2005 or higher.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 01:30:20

or

select * from store
where deliveryDate>=dateadd(day,datediff(day,0,getdate()),0) and
deliveryDate<dateadd(day,datediff(day,0,getdate()),1)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 01:30:58

quote:
Originally posted by Peso

select top 1 with ties *
from store
where DATEDIFF(DAY, deliveryDate, GetDate() ) = 0
ORDER BY deliveryDate

E 12°55'05.63"
N 56°04'39.26"

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-11 : 03:39:35

Easier. I musthave had a brain freeze last night.

select *
from store
where DATEDIFF(DAY, deliveryDate, GetDate() ) = 0

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources