Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Finding the Correct Weekday Regardless of DateFirst Expand / Collapse
Author
Message
Posted Thursday, February 18, 2010 10:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 6:53 AM
Points: 140, Visits: 524
Comments posted to this topic are about the item Finding the Correct Weekday Regardless of DateFirst

--Divya
Post #868760
Posted Thursday, February 18, 2010 10:20 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:00 AM
Points: 522, Visits: 2,013
In many cases, I can do what I have to do by simply capturing the weekday values for a known saturday and sunday, like so:

declare @dowSat int
declare @dowSun int
set @dowSat=datepart(dw,'20061028')
set @dowSun=datepart(dw,'20061029')

Post #868764
Posted Thursday, February 18, 2010 11:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
Nice tip, Divya. I'm adding this to my bookmarks next to that for "Some Common Date Routines" that Lynn Pettis put up a while ago.

---
Edit to correct link
Post #868771
Posted Thursday, February 18, 2010 11:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 6:53 AM
Points: 140, Visits: 524
Yes Michael, that is certainly there. My post gives an idea of forthcoming weekends without having any intimation of dates.

--Divya
Post #868778
Posted Thursday, February 18, 2010 11:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 6:53 AM
Points: 140, Visits: 524
Thanks John.
I am not able to open Lynn Pettis post..


--Divya
Post #868780
Posted Friday, February 19, 2010 12:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 19, 2011 10:39 PM
Points: 1, Visits: 35
Good Article Divya....

This is Another way..

set datefirst 2
select datediff(dd,5,'12/17/2009')%7
set datefirst 5
select datediff(dd,5,'12/17/2009')%7
Post #868783
Posted Friday, February 19, 2010 12:17 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 06, 2014 1:47 AM
Points: 292, Visits: 1,084
Excellent Article Divya.....
Satnam
Post #868786
Posted Friday, February 19, 2010 12:58 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 12:23 AM
Points: 5, Visits: 27
This is a great tip. Thanks.

Can I raise a question regarding it though?

Maybe I'm misreading but if the date you pick is Saturday (instead of Thursday in the example - 17th Dec 2009 was a Thursday I believe), unless I'm very much mistaken by the values returned from modulo, the final result after applying the modulo will be zero. Seven needs to be added in this case.
Post #868800
Posted Friday, February 19, 2010 1:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 2,393, Visits: 2,286
This code is indipendent from datefirst:
Monday is 0 and Sunday is 6
SELECT cast(cast('20100215' as datetime) as int)%7
SELECT cast(cast('20100216' as datetime) as int)%7
SELECT cast(cast('20100217' as datetime) as int)%7
SELECT cast(cast('20100218' as datetime) as int)%7
SELECT cast(cast('20100219' as datetime) as int)%7
SELECT cast(cast('20100220' as datetime) as int)%7
SELECT cast(cast('20100221' as datetime) as int)%7
Post #868816
Posted Friday, February 19, 2010 1:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369
SELECT 1 + DATEDIFF(DAY, 0, '20091217') % 7




N 56°04'39.16"
E 12°55'05.25"
Post #868819
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse