Happy Monday

Question

Post reply

Happy Monday

Steve Jones - SSC Editor

SSC Guru

Points: 738773
More actions
August 30, 2014 at 1:09 pm

#397278

Comments posted to this topic are about the item Happy Monday

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104768 More actions · Answer 1

Fun question.

Very nice function - easy to read and see what it does and how it does it without the documentation.

Tom

Hany Helmy SSChampion Points: 13921 More actions · Answer 2

Nice question & nice function, thanx 4 the 7 pts. 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 3

Good function.

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 4

PRAMANA.DBA

SSCertifiable

Points: 5507

September 1, 2014 at 12:39 am

#1742150

Vimal Lohani (8/31/2014)
Good function.

robertjtjones SSC-Addicted Points: 484 More actions · Answer 5

Lol - had to look up what Labor Day was (from across the pond 😉 )

philip.cullingworth SSCrazy Points: 2150 More actions · Answer 6

I'm confused.

In the question we have the following 2 lines

SELECT @sDayName = LEFT(UPPER(LTRIM(RTRIM(@sDayName))),3)

, @dtFirstOfMonth = CONVERT (SMALLDATETIME

, CONVERT(CHAR(4), @nYear)

+ '-' + CONVERT(VARCHAR(2), @nMonth)

+ '-' + CONVERT(VARCHAR(2), 1)

, 110 -- required for determinism

)

SELECT @nFirstIsOnAdw =

1 + (datediff (d

, Convert(datetime, '1899-12-31', 120)

, @dtFirstOfMonth

)

% 7

)

I assumed that the different convert format codes was probably a typo but looked up the formats and 110 converts as "mm-dd-yyyy" and 120 as "yyyy-mm-dd" (http://msdn.microsoft.com/en-GB/library/ms187928.aspx). Can someone explain why the first conversion with format 110 works when the string is the wrong format? Incidently if you change the code to 103 (expecting dd/mm/yyyy) then the first convert (of 2014-9-1) converts to 9th Jan 2014.

Thanks.

Louisa Whitton SSCrazy Points: 2577 More actions · Answer 7

For amusement, you could try the following:

select convert(smalldatetime,'9-2014-1',110)

This returns 1st Sept 2014.

It appears that it doesn't matter where in the string you put the year!

Phil Factor SSC-Insane Points: 20254 More actions · Answer 8

Just out of interest, This is possibly a lot more difficult to understand, but shorter. (the same code will detect the nth day you specify. I just stripped it down a bit to do the first monday! See https://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/ )

[font="Courier New"]

IF OBJECT_ID (N'firstMondayOfMonth') IS NOT NULL

DROP FUNCTION firstMondayOfMonth

GO

CREATE FUNCTION firstMondayOfMonth (@TheYear CHAR(4), @TheMonth CHAR(3))

RETURNS DATETIME

WITH EXECUTE AS CALLER

AS

BEGIN

RETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date,'1 '+@TheMonth+' '+@TheYear,113)), 0)+6

-(DATEPART (Weekday, DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date,'1 '+@TheMonth+' '+@TheYear,113)), 0))

+@@DateFirst+4)%7

END

GO

IF (

SELECT dbo.FirstMondayOfMonth ('2012','jun'))<> '2012-06-04'

RAISERROR('''firstMondayOfMonth'' stopped working (1)',16,1)

IF (

SELECT dbo.FirstMondayOfMonth ('2014','Sep'))<> '2014-09-01'

RAISERROR('''firstMondayOfMonth'' stopped working (2)',16,1)

[/font]

Best wishes,
Phil Factor

Gazareth One Orange Chip Points: 27737 More actions · Answer 9

robertjtjones (9/1/2014)
Lol - had to look up what Labor Day was (from across the pond 😉 )

Me too. Although google returned '1st May 2014' in massive letters so I got it wrong, d'oh!

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 10

Thank you for the post, SJ, interesting one. Initially there were lot of confusion for me after reading QToD, as knowing May 1 as International Worker's Day and so it also applies to US too... well no. In India, we have on May 1st (http://en.wikipedia.org/wiki/International_Workers%27_Day#India) and now I see in wiki it states "In the United States, efforts to switch Labor Day from September to May 1 have not been successful".

Unique holiday, where it fixes on the first occurrence specific day and not on the date. Never knew this.

From wiki: Labor Day (US)

Date

First Monday in September

2013 date

September 2

2014 date

September 1

2015 date

September 7

2016 date

September 5

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

SqlMel SSCrazy Points: 2901 More actions · Answer 11

+7!

Thanks for the question, Steve.

Very good function, btw.

---------------
Mel. 😎

Xavon SSCrazy Points: 2280 More actions · Answer 12

Gazareth (9/1/2014)
robertjtjones (9/1/2014)
Lol - had to look up what Labor Day was (from across the pond 😉 )
Me too. Although google returned '1st May 2014' in massive letters so I got it wrong, d'oh!

Yeah, Google takes your location and history into account, so you have to be careful how you phrase things some times.

--------------

Interesting function. Useful for figuring out all those Xth day of Y month holidays; Mother's Day, Father's Day, Labor Day, Thanksgiving, etc.

Memorial Day would require a bit more work:

SELECT ISNULL(dbo.udf_DT_NthDayInMon('2014', 5, 5, 'Mon'), dbo.udf_DT_NthDayInMon('2014', 5, 4, 'Mon'));

g.britton SSChampion Points: 13863 More actions · Answer 13

I'm not sure how "Labor Day 2014" can be returned. I don't see the string "Labor Day" anywhere in the function.

Gerald Britton, Pluralsight courses

Richard Warr SSCertifiable Points: 6968 More actions · Answer 14

g.britton (9/1/2014)
I'm not sure how "Labor Day 2014" can be returned. I don't see the string "Labor Day" anywhere in the function.

The function returns Labor(sic) Day 2014 as opposed to "Labor Day 2014".

This appears to be the day when nobody in the US does any labour.