previous occurance of wednesday if not Wednesday

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

previous occurance of wednesday if not Wednesday

Author

Topic

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2011-12-15 : 05:36:29

i want a sql statement which gives me the previous occurence of a wednesday

selecting
12/15(Thu) gives 12/14(Wed)
12/13(Tue) gives 12/07(Wed)
12/14(Wed) gives 12/14(Wed)
12/09(Sat) gives 12/07(Wed)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 05:41:07

[code]
SELECT DATEADD(dd,((DATEDIFF(dd,0,@Yourdatevalue)/7) *7)+2,0)
[/code]

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

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2011-12-15 : 05:45:23

Sorry but this does not work! 12/13 gives me 12/14 but it should give me 12/07

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 05:48:29

SELECT DATEADD(dd,((DATEDIFF(dd,0,@Yourdatevalue)/7) *7)-5,0)

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

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2011-12-15 : 05:57:14

12/15 gives me 12/07, it should give me 12/14 the last occuring wednesday!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 06:50:33

ok then merge both


declare @Date datetime
set @Date='16 dec 2011'--give some date value here

SELECT DATEADD(dd,(DATEDIFF(dd,0,@Date)/7) *7  ,0)+ CASE WHEN DATEADD(dd,(DATEDIFF(dd,0,@Date)/7) *7  ,0) < @Date THEN + 2 ELSE -5 END

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources