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: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
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: Friday, September 19, 2014 11:59 PM
Points: 542, Visits: 2,122
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
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: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
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 6, 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: Yesterday @ 7:33 AM
Points: 2,542, Visits: 2,410
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: Sunday, September 28, 2014 12:23 AM
Points: 2,397, Visits: 3,411
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