Author |
Topic |
dutchgold647
Starting Member
13 Posts |
Posted - 2013-06-19 : 22:43:37
|
Hi everyone,I'm trying to get a MS - SQL query to run so that if todays day is Thursday or Friday, it runs for a date scheduled 4 days away. If it's any other day, it's to run for a date scheduled 2 days away.Below is what I've got at the moment so any help anyone has will be much appreciated.SELECT [ID] ,[No] ,[DateSched] FROM [TransSheet] Where DateSched between case when DATEPART(DW, GETDATE()) in (5,6) then DATEADD(DD, DATEDIFF(DD,0,GETDATE()+4),0) and DATEADD(DD, DATEDIFF(DD,0,GETDATE()+4),0) Else DATEADD(DD, DATEDIFF(DD,0,GETDATE()+2),0) and DATEADD(DD, DATEDIFF(DD,0,GETDATE()+2),0) EndThanks everyone |
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-06-19 : 23:01:42
|
[code]SELECT[ID],[No],[DateSched]FROM TransSheetWhere (((DATEPART(DW, getdate()) IN (5,6)) and (DateSched = cast(getdate()+4 as date))) or ((DATEPART(DW, getdate()) NOT IN (5,6)) and (DateSched = cast(getdate()+2 as date))))[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 00:48:11
|
The below solution is independent of any server settingsAll the previous ones rely upon DATEFIRST settingSELECT [ID],[No],[DateSched]FROM [TransSheet]Where DateSched >=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 IN (2,3) THEN 4 ELSE 2 END)AND DateSched < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 IN (2,3) THEN 5 ELSE 3 END) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
dutchgold647
Starting Member
13 Posts |
Posted - 2013-06-20 : 03:28:45
|
and the winner is waterduck!thank you to you both for helping me out here.cheers |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 03:33:27
|
quote: Originally posted by dutchgold647 and the winner is waterduck!thank you to you both for helping me out here.cheers
Does that mean my solution didnt work for you?Both the solutions are equivalent except for the fact that one relies upon DATEFIRST setting of the server whilst mine doesnt and works irrespective of it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
dutchgold647
Starting Member
13 Posts |
Posted - 2013-06-20 : 03:40:46
|
Hi visakh16,Your solution worked perfectly I said the winner was waterduck because he got there first.Nothing wrong with your solution though.Cheers, |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 03:45:34
|
quote: Originally posted by dutchgold647 Hi visakh16,Your solution worked perfectly I said the winner was waterduck because he got there first.Nothing wrong with your solution though.Cheers,
Ok..FineI always prefer writing server independent code as its more flexible and can be easily ported to other servers without worrying on regional/server settingshttp://visakhm.blogspot.com/2012/08/creating-server-independent-day.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2013-06-20 : 03:48:12
|
Visakh raised a valid point that is often overlooked and has the potential to come back and bite you at some point.A change in the DATEFIRST setting will render incorrect results.Duane.http://ditchiecubeblog.wordpress.com/ |
|
|
dutchgold647
Starting Member
13 Posts |
Posted - 2013-06-20 : 03:52:17
|
Ah ok cool, thanks visakh16I'm pretty new to all this so didn't really understand the difference so thanks for pointing it out. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 03:56:30
|
No problemyou're welcome just wanted you to be aware of the raised point------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|