Post reply

Week in month

  • March 26, 2004 at 5:46 am

    #107466

    I found this wonderful function from Antares686 in response to a question, but I don't quite understand how it works.

    CREATE FUNCTION dbo.WeekInMonth (@date datetime)

    RETURNS tinyint

    AS

    BEGIN

     DECLARE @out tinyint

     SET @out = (DATEPART(wk,@date) - DATEPART(wk,DATEADD(m,DATEDIFF(m,0,@date),0))) + 1

     RETURN ( @out )

    END

    The part that throws me is the "DATEDIFF(m,0,@date)" portion.  The 0 in this statement is where the start date goes.  BOL states the startdate is subtracted from the enddate. 

    1.  How can a date be subtracted from 0?

    2.  The value I used for @Date was 3/26/04.  When this was subtracted from 0, the result was 1250.  What does the 1250 represent?

    I would appreciate any explanations on how the statement "DATEPART(wk,DATEADD(m,DATEDIFF(m,0,@date),0))) " works.

    Thanks,

    G

  • March 26, 2004 at 6:25 am

    #500625

    With regards to dates 0 is 1/1/1900.

    1) Yes to an extent you can subtract from 0.

    2) 1250 represent the number of mon ths that have occurred since 1/1/1900.

    So by figuring the difference between a given date and 1/1/1900 the adding that number of months back to 1/1/1900 you easily get the first day of the month for the given date provided in the DATEDIFF section.

    There was a thread a long time back where we were doing it to get the DATE with time of midnight based on GETDATE where I first saw something like this. Sorry don't have link to right off.

     

    Hope this helps.

  • March 26, 2004 at 6:27 am

    #500627

    aw shucks, Antares beat me to it, this is my explanation

    SQL Server stores its dates in binary and the lowest value allowed is 0. Any time you use a number as a date SQL Server will convert it (0 = 1900-01-01).

    So when using DATEDIFF(m,0,@date) SQL Server will convert 0 to 1900-01-01 and work out the number of months between this date and the end date (2004-03-26) and will give you 1250.

    DATEADD(m,DATEDIFF(m,0,@date),0) will add the number of months to the date 0 (1900-01-01) and will give you 2004-03-01, the date of the first day of the month of the date variable.

    DATEPART(wk,DATEADD(m,DATEDIFF(m,0,@date),0)) will use the date created and will give the week number of the first day of the month

    DATEPART(wk,@date) will give the week number of the date in the variable

    Subtract one from the other and add 1 will give the week number in the month of the date specified

    Far away is close at hand in the images of elsewhere.
    Anon.

  • March 26, 2004 at 6:31 am

    #500628

    Thank you both so much.  I now understand what the function is doing.  Very clever procedure!

Viewing 4 posts - 1 through 3 (of 3 total)

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