date formats - SQL Server Forums

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

date formats

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-30 : 09:55:59

I am trying to use this query to pull products which expire the next day:

select *
from products
where [Exp Date]=DATEADD(day,1,getdate())

the [exp date] column has test values of 3/30/2010,3/31/2010, and 4/1/2010. This query should pull only the values with the date of 3/31/2010 but it doesn't return anything. If I change it to this:

select *
from products
where [Exp Date]<=DATEADD(day,1,getdate())

it will pull the 3/31/2010 values but also the 3/30/2010 because of the less than sign.
Am I missing something that happens when using getdate or adding dates that might swing the [exp date] to something other than what is originally there?

EDIT - I'm not sure why I couldn't just set it equal to the next day, but a quick workaround is:

where [Exp Date]<=DATEADD(day,1,getdate()) and [Exp Date]>GETDATE()

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-30 : 10:04:00

Have a look at your [Exp Date]. how does it look?
Have a look at the result from select DATEADD(day,1,getdate()). how does it look?

Is there a difference because of the time part?

No, you're never too old to Yak'n'Roll if you're too young to die.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-03-30 : 10:05:07

DATEADD(day,1,getdate()) will add a time stamp. If your data is as you stated, you may want to try:

CONVERT(varchar(20),GETDATE() + 1,101)

This strips the time off and puts it into the format of your test values.

edit - Sorry Webfred!

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-30 : 10:15:39

Ah, thanks guys - didn't think it would add a timestamp to it. Changed it up so time won't mess me up in the future.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-30 : 11:11:36

What is the datatype of [Exp Date]?

Also try

select *
from products
where [Exp Date]=DATEADD(day,DATEDIFF(day,0,getdate())+1,0)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:06:47

if it has timepart use

where [Exp Date]>=DATEADD(day,DATEDIFF(day,0,getdate())+1,0)
AND [Exp Date< DATEADD(day,DATEDIFF(day,0,getdate())+2,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources