Month and a day - 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 2000 Forums

SQL Server Development (2000)

Month and a day

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2002-04-18 : 11:46:57

I want to select records that were updated 6 months and a day ago,
datediff(month, Company_Updates.date_updated , getdate()) = 6 gives me all records from 6 months ago but ignores days..??

Nazim
A custom title

1408 Posts

Posted - 2002-04-18 : 11:49:42

datediff(month, Company_Updates.date_updated-1 , getdate()) = 6

HTH

--------------------------------------------------------------

jackstow
Posting Yak Master

160 Posts

Posted - 2002-04-18 : 12:02:41

Thanks, but I don't see how that would work - maybe I haven't explained myself..

If today is 18/04/2002 I want to select records updated on 17/10/2001 but not 16/10/2001 or 18/10/2001

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-18 : 12:13:09

Perhaps one of these four:

Company_Updates.date_updated = DATEADD(day, -1, DATEADD(month, -6, GETDATE()))
Company_Updates.date_updated = DATEADD(month, -6, DATEADD(day, -1, GETDATE()))
DATEADD(month, 6, DATEADD(day, 1, Company_Updates.date_updated)) = GETDATE()
DATEADD(day, 1, DATEADD(month, 6, Company_Updates.date_updated)) = GETDATE()

Yes, they all produce different results!

Edited by - Arnold Fribble on 04/18/2002 12:27:04

jackstow
Posting Yak Master

160 Posts

Posted - 2002-04-18 : 12:21:28

Well this seems to work..

where
datediff(month, Company_Updates.date_updated , getdate()) = 6
and
datepart(dd, Company_Updates.date_updated) = datepart(dd, getdate() - 1)

..answered my own question!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-18 : 12:24:30

But by that definition, there are days when there is no day that is "six months and one day" ago. For example, 2001-08-30.

Edited by - Arnold Fribble on 04/18/2002 12:25:47

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-18 : 12:30:41

The only thing wrong with Arnold's formula is the timestamp part of the datetime value, this should do it:

WHERE DateDiff(dd, Company_Updates.date_updated, DATEADD(month, -6, GETDATE()))=-1

jackstow
Posting Yak Master

160 Posts

Posted - 2002-04-18 : 12:38:51

quote:
But by that definition, there are days when there is no day that is "six months and one day" ago. For example, 2001-08-30

- yes just realised that myself! Doh!

robvolks code is right on the money.

Thanks

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-18 : 12:41:58

Oops, yes, bad assumption on my part there. I still stand by what I said about the 4 possibilities being different though. Months are tricky little buggers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources