Post reply

Happy Monday

  • August 30, 2014 at 1:09 pm

    #397278

    Comments posted to this topic are about the item Happy Monday

  • August 30, 2014 at 2:58 pm

    #1742083

    Fun question.

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

    Tom

  • August 31, 2014 at 12:35 am

    #1742098

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

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • August 31, 2014 at 10:25 pm

    #1742136

    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

  • September 1, 2014 at 12:39 am

    #1742150

    Vimal Lohani (8/31/2014)

    Good function.

  • September 1, 2014 at 1:39 am

    #1742163

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

  • September 1, 2014 at 1:55 am

    #1742166

    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.

  • September 1, 2014 at 2:01 am

    #1742167

    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!

  • September 1, 2014 at 2:43 am

    This was removed by the editor as SPAM

  • September 1, 2014 at 2:59 am

    #1742185

    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

  • September 1, 2014 at 4:10 am

    #1742199

    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!

  • September 1, 2014 at 5:02 am

    #1742208

    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.

  • September 1, 2014 at 5:57 am

    #1742214

    +7!

    Thanks for the question, Steve.

    Very good function, btw.

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

  • September 1, 2014 at 6:31 am

    #1742222

    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'));

  • September 1, 2014 at 9:09 am

    #1742241

    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

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

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