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 ««12

get date of first monday of the given year? Expand / Collapse
Author
Message
Posted Friday, August 19, 2011 7:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 21,385, Visits: 9,603
ChrisM@Work (8/19/2011)
Ninja's_RGR'us (8/19/2011)
Quite easy with a calendar table.

Select top 1 * FROM dbo.Calendar WHERE Y = 2011 AND DW = <whatever fits your setting> ORDER BY dt.


Just as easy without. Make one for the first week of the year and pick the correct row from it


Too many good uses to not have 1.


Since my table is less than 2 MB and that I can query the table with an <clustered>index I don't really see the point of trying to go much faster than that. Not saying it's impossible, just never had that need!
Post #1162448
Posted Friday, August 19, 2011 12:27 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
Ninja's_RGR'us (8/19/2011)
ChrisM@Work (8/19/2011)
Ninja's_RGR'us (8/19/2011)
Quite easy with a calendar table.

Select top 1 * FROM dbo.Calendar WHERE Y = 2011 AND DW = <whatever fits your setting> ORDER BY dt.


Just as easy without. Make one for the first week of the year and pick the correct row from it


Too many good uses to not have 1.


Since my table is less than 2 MB and that I can query the table with an <clustered>index I don't really see the point of trying to go much faster than that. Not saying it's impossible, just never had that need!

I agree with Remi on this one. There are just too many uses for calendar tables not to have them. When properly done they give better performance than complicated date math in most cases. You can spell out a month name any way you want in any language and have it displayed correctly. Very simple aggregating and grouping. All sorts of uses.
Todd Fifield
Post #1162727
Posted Friday, August 19, 2011 12:50 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:22 AM
Points: 3,122, Visits: 11,406
The code to find the first Monday of the year is fairly simple:
First find the 7th day of the year (Jan 7):
dateadd(yy,datediff(yy,0,a.DT),6)

and then find the Monday on or before that date:
dateadd(dd,(datediff(dd,0, JanuarySeventh )/7)*7,0)

Does not depend on any setting of language or datefirst.

select
DT,
FirstMonday =
dateadd(dd,(datediff(dd,0,dateadd(yy,datediff(yy,0,a.DT),6))/7)*7,0)
from
( -- Test Data
select DT = getdate() union all
select DT = '20111231' union all
select DT = '20121231' union all
select DT = '20131231' union all
select DT = '20141231' union all
select DT = '20110101' union all
select DT = '20080229'
) a


Results:
DT                       FirstMonday
------------------------ -----------------------
2011-08-19 14:42:26.310 2011-01-03 00:00:00.000
2011-12-31 00:00:00.000 2011-01-03 00:00:00.000
2012-12-31 00:00:00.000 2012-01-02 00:00:00.000
2013-12-31 00:00:00.000 2013-01-07 00:00:00.000
2014-12-31 00:00:00.000 2014-01-06 00:00:00.000
2011-01-01 00:00:00.000 2011-01-03 00:00:00.000
2008-02-29 00:00:00.000 2008-01-07 00:00:00.000
Post #1162735
Posted Tuesday, August 23, 2011 2:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, January 17, 2014 6:38 AM
Points: 278, Visits: 534
That's the same algorithm I posted further up the thread, although rather better explained.
The only assumption that is made is that you know a Monday, which in your case is day 0.

I rather unnecessarily tied myself in knots trying to make that assumption explicit and avoid dates before February 1900, to avoid leap year confusions.
Post #1163775
Posted Tuesday, August 23, 2011 9:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:22 AM
Points: 3,122, Visits: 11,406
paul_ramster (8/23/2011)
That's the same algorithm I posted further up the thread, although rather better explained.
The only assumption that is made is that you know a Monday, which in your case is day 0.

I rather unnecessarily tied myself in knots trying to make that assumption explicit and avoid dates before February 1900, to avoid leap year confusions.


If you are concerned about dates before 1900-01-01, then you could use 1753-01-01, which is the earliest possible datetime and also a Monday. Ask me how I knew that.


Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307




Post #1164080
Posted Tuesday, August 23, 2011 9:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 21,385, Visits: 9,603
Michael Valentine Jones (8/23/2011)
paul_ramster (8/23/2011)
That's the same algorithm I posted further up the thread, although rather better explained.
The only assumption that is made is that you know a Monday, which in your case is day 0.

I rather unnecessarily tied myself in knots trying to make that assumption explicit and avoid dates before February 1900, to avoid leap year confusions.


If you are concerned about dates before 1900-01-01, then you could use 1753-01-01, which is the earliest possible datetime and also a Monday. Ask me how I knew that.


Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307




Real old mother in law?


I know I learned that from our ERP (MS Dynamics / Navision)
Post #1164087
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse