SQL question - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL question

Author

Topic

hulstj
Starting Member

2 Posts

Posted - 2011-07-19 : 11:33:55

Here are two select statements, the first one gives no result, the other one gives the proper result.

Reason why?

select dati from trxhdr
where dati between dateadd(day, -120, GETDATE()) and GETDATE()

select dati from trxhdr
where dati between dateadd(day, -120, '2011-07-15') and GETDATE()

Thanks Jos

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-07-19 : 11:47:10

What's the value of dati that is being returned?

hulstj
Starting Member

2 Posts

Posted - 2011-07-19 : 12:14:17

quote:
Originally posted by BruceT

What's the value of dati that is being returned?

Statement:
select dati from dbo.trxhdr
where dati > DATEADD(day, -600, '2011-07-15 04:00:00')

Returns
2010-02-28 04:51:23.000
2010-02-28 06:30:21.000
2010-02-28 06:58:59.000
2010-02-28 07:02:42.000
2010-02-28 09:58:45.000
etcetera

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-19 : 14:37:07

-600 is completely different to -120
You should show what

select dati from trxhdr
where dati between dateadd(day, -120, '2011-07-15') and GETDATE()

is giving.
Because -120 from 2011-07-15 is not equal to -120 from 2011-07-19 (getdate()).

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-19 : 14:50:15

Also
dateadd(day, -120, GETDATE()) gives you
2011-03-21 13:47:55.737
so you'd miss everything before this time on March 3; using '20110715' gives you everything that happened
that happened on dateadd(day, -120, '2011-07-15')
Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources