Newbie SQL Date 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

General SQL Server Forums

New to SQL Server Programming

Newbie SQL Date Question

Author

Topic

cronas
Starting Member

3 Posts

Posted - 2009-09-29 : 06:30:21

Hi all, I'm new to the site and I hope you can help as I can't find an answer anywhere. I basically need to write a piece of SQL that will find the previous 1st July based on today's date.

Example:

Today's Date = 29/09/2009
Previous July = 01/07/2009

Today's Date = 02/07/2010
Previous July = 01/07/2010

I know it should be simple but I can't get my head around it

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 06:33:33

declare @date datetime
set @date='20090929'
select dateadd(month,6,dateadd(year,datediff(year,0,@date),0))

Madhivanan

Failing to plan is Planning to fail

cronas
Starting Member

3 Posts

Posted - 2009-09-29 : 06:54:34

thanks for the quick response, that is almost there but not queit right. if todays date was the 01/01/2010 or 30/06/2010 it returns 01/07/2010 when it should still be 01/07/2009. it should only every return the previous 01/07 never the next one?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 07:05:30

declare @date datetime
set @date='20100630'
select dateadd(year,-case when month(@date)<7 then 1 else 0 end,dateadd(month,6,dateadd(year,datediff(year,0,@date),0)))

Madhivanan

Failing to plan is Planning to fail

cronas
Starting Member

3 Posts

Posted - 2009-09-29 : 07:29:18

Thank you so much this looks like it is doing the trick I will do some more intense testing but I can't thank you enough. here is the finish SQL using getdate encase anyone else has a similar question.

declare @date datetime
set @date= getdate()
select dateadd(year,-case when month(@date)<7 then 1 else 0 end,dateadd(month,6,dateadd(year,datediff(year,0,@date),0)))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 08:04:03

quote:
Originally posted by cronas

Thank you so much this looks like it is doing the trick I will do some more intense testing but I can't thank you enough. here is the finish SQL using getdate encase anyone else has a similar question.

declare @date datetime
set @date= getdate()
select dateadd(year,-case when month(@date)<7 then 1 else 0 end,dateadd(month,6,dateadd(year,datediff(year,0,@date),0)))

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources