Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » get date of first monday of the given year?
Add to briefcase
16 posts, Page 2 of 2
««12

get date of first monday of the given year? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Ninja's_RGR'us
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 @ 7:02 PM
Points: 21,376, Visits: 9,584
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
tfifield
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: Friday, April 05, 2013 8:10 PM
Points: 958, Visits: 2,873
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
Michael Valentine Jones
Posted Friday, August 19, 2011 12:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:30 PM
Points: 2,969, Visits: 10,617
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
paul_ramster
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: Yesterday @ 3:33 AM
Points: 276, Visits: 525
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
Michael Valentine Jones
Posted Tuesday, August 23, 2011 9:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:30 PM
Points: 2,969, Visits: 10,617
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
Ninja's_RGR'us
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 @ 7:02 PM
Points: 21,376, Visits: 9,584
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
16 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.