Need SQL to render FIRST day of the year

  • Need SQL to render FIRST day of the year - based on select(getdate)

    Examples:

    If today's date is Mar 14, 2005 -- I need to render Jan 1, 2005 00:00:00.000

    If today's date is Nov 29, 2005 -- I need to render Jan 1, 2005 00:00:00.000

    If today's date is Jun 23, 2006 -- I need to render Jan 1, 2006 00:00:00.000

    Idea's?

    BT
  • --FIRST DAY of CURRENT MONTH

    select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

    --FIRST Day of the Year

    select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

    --FIRST Day of the Quarter

    select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

    --LAST Day of Prior Month

    select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))

    --LAST Day of Prior Year

    select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))

    --LAST Day of Current Month

    select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))

    --LAST Day of Current Year

    select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))

    --FIRST Monday of the Month

    select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)     

    BT
  • Since everything else in the results you want is a constant but for the year, why not just good old concatenation...?!?!

    select 'Jan 1, ' + cast(datepart(yyyy, getdate()) as varchar) + ' 00:00:00.000'







    **ASCII stupid question, get a stupid ANSI !!!**

  • ISO is better:

    SELECT CAST(YEAR(GETDATE()) AS varchar(4))+'0101'

    Works for any SET DATEFORMAT or language setting on client or server.

    Andy

  • How do you get to the First Day of Prior Month?

    I'm trying to use an agent to automate a monthly report that will, without having to input a date, render results for the last calendar month. I'm using the following to get results for the previous day, but need the syntax to get previous calendar month.

    SELECT COUNT(*) AS [Total Calls]

    FROM History

    WHERE (ProjectID IN (1013, 1014)) AND (CallDateTime BETWEEN GETDATE() -1 AND GETDATE())

  • Here's a udf I put together that takes a date input and returns a selected date - since our system only uses dates in the mmddyyy or yyyymmdd format, it only addresses those instances but could be easily extended to other date formats.

    --INPUTS:

      --RunCode1 - what date to return - see below for codes

      --RunCode2 - input date format - 1=mmddyyyy, 2=yyyymmdd - NOT USED AT PRESENT

      --RunCode3 - output date format - 1=mmddyyyy, 2=yyyymmdd - also used as default format if RunCode3 value out of range

    --TO TEST: select dbo.udf_getSelectedDate('20051013',101,2,2)

    CREATE FUNCTION dbo.udf_getSelectedDate

       (@DateIn varchar(8),

        @RunCode1 int,

        @RunCode2 int,

        @RunCode3 int)

    RETURNS

      varchar (8)

    AS

    BEGIN

       DECLARE @DateInDT datetime -- date in formatted as datetime

       DECLARE @DateOutDT datetime -- date out formatted as datetime

       DECLARE @DateOut varchar(8) -- date out formatted as varchar

       -- format input

       SET @DateInDT = convert(datetime, @DateIn)

       -- calc the date to be returned

       IF @RunCode1 = 101

          --101 - FIRST Day of Month - selected date

          SET @DateOutDT = DATEADD(mm,DATEDIFF(mm,0,@DateInDT), 0)

       ELSE IF @RunCode1 = 102

          --102 - FIRST Day of Next MonthSET - selected date

          SET @DateOutDT = DATEADD(mm,DATEDIFF(mm,0,@DateInDT)+1, 0)

       ELSE IF @RunCode1 = 103

          --103 - FIRST Day of Prior Month - selected date

          SET @DateOutDT = DATEADD(mm,DATEDIFF(mm,0,@DateInDT)-1, 0)

       ELSE IF @RunCode1 = 104

          --104 - LAST Day of Current Month - selected date

          SET @DateOutDT = DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@DateInDT)+1, 0))

       ELSE IF @RunCode1 = 105

          --105 - LAST Day of Next Month - selected date

          SET @DateOutDT = DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@DateInDT)+2, 0))

       ELSE IF @RunCode1 = 106

          --106 - LAST Day of Prior Month - selected date

          SET @DateOutDT = DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@DateInDT), 0))

       ELSE IF @RunCode1 = 201

          --201 - FIRST Day of Current Year - selected date

          SET @DateOutDT = DATEADD(yy,DATEDIFF(yy,0,@DateInDT), 0)

       ELSE IF @RunCode1 = 202

          --202 - FIRST Day of Next Year - selected date

          SET @DateOutDT = DATEADD(yy,DATEDIFF(yy,0,@DateInDT)+1, 0)

       ELSE IF @RunCode1 = 203

          --203 - FIRST Day of Prior Year - selected date

          SET @DateOutDT = DATEADD(yy,DATEDIFF(yy,0,@DateInDT)-1, 0)

       ELSE IF @RunCode1 = 204

          --204 - LAST Day of Current Year - selected date

          SET @DateOutDT = DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,@DateInDT)+1, 0))

       ELSE IF @RunCode1 = 205

          --205 - LAST Day of Next Year - selected date

          SET @DateOutDT =  DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,@DateInDT)+2, 0))

       ELSE IF @RunCode1 = 206

          --206 - LAST Day of Prior Year - selected date

          SET @DateOutDT =  DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,@DateInDT), 0))

       ELSE IF @RunCode1 = 301

          --301 - FIRST Day of Current Quarter

          SET @DateOutDT =  DATEADD(qq,DATEDIFF(qq,0,@DateInDT), 0)

       ELSE IF @RunCode1 = 302

          --302 - FIRST Day of Next Quarter

          SET @DateOutDT =  DATEADD(qq,DATEDIFF(qq,0,@DateInDT)+1, 0)

       ELSE IF @RunCode1 = 303

          --303 - FIRST Day of Last Quarter

          SET @DateOutDT =  DATEADD(qq,DATEDIFF(qq,0,@DateInDT)-1, 0)

       ELSE IF @RunCode1 = 304

          --304 - LAST Day of Current Quarter

          SET @DateOutDT =  DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,@DateInDT)+1, 0))

       ELSE IF @RunCode1 = 305

          --305 - LAST Day of Next Quarter

          SET @DateOutDT =  DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,@DateInDT)+2, 0))

       ELSE IF @RunCode1 = 306

          --306 - LAST Day of Last Quarter

          SET @DateOutDT =  DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,@DateInDT), 0))

       ELSE IF @RunCode1 = 401

          --401 - FIRST Monday of Current Month

          SET @DateOutDT =  DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,@DateInDT),@DateInDT)), 0)  

       -- format output

       IF @RunCode3 = 1

          SET @DateOut = SUBSTRING(CONVERT(varchar(8), @DateOutDT, 12),3, 4) + CONVERT(varchar(4),DATEPART(YEAR, @DateOutDT) )   -- set display format to mmddyyyy

       ELSE

          SET @DateOut = CONVERT(varchar(4),DATEPART(YEAR, @DateOutDT) ) + SUBSTRING(CONVERT(varchar(8), @DateOutDT, 12),3, 4)   -- set display format to yyyymmdd

    RETURN (@DateOut)

    END

     

  • Harley,

    That's an outstanding idea!  Thank you.  It's nice to see people treating SQL as a computer language instead of just a repository.  I do have a couple of suggestions, though...

    1. Numeric codes are difficult to remember and the function would require either external "bedside" documentation of you'd need to make it return it's own method of usage if "Help" were entered as each of the parameters.  Functions should be easy and intuitive to use.  "Intuitive" in this case means "almost English" and something VERY easy to remember. 

    2. Make a function only do 1 thing and make it do it well.  In this case, that 1 thing should be to get the desired date... not do any formatting.  Create a different function to do the formatting so it may also be used elsewhere.  Do not call a function in a function because that violates the idea of doing one thing well in any given function.

    3. Your function is limited to Current, Previous, and Next month.  Your head is definitely in the right place but limiting a function like that violates the whole idea of having such a function.  It needs to be able to calculate, say, the first day of 20 months from now or 5 years ago.  You get the idea.

    4. In the code where it's used, it should not only be readable, but it should be VERY obvious what the function is doing without having to lookup how to use the function.  In other words, it should be self-documenting.

    With all of those things in mind, I offer the following code as a suggestion for your future endeavors in this area... documentation included... since I'm a bit anal about documentation, you'll find that the embedded documentation is actually longer than code.

     CREATE FUNCTION dbo.udf_GetSelectedDate

    /**************************************************************************************************

     Purpose:

     This function accepts multiple parameters to identify a "period" and a date contained in that

     period.  Based on other parameters, it will return the first day of that period, or the last.

     Usage:

     dbo.udf_GetSelectDate(@Boundary,@PeriodOffSet,@Period,@DateIn)

     Parameter descriptions and possible values:

     @Boundary: Describes the desired day of period

     1.  Possible values are 'First of' (not trapped) and 'Last of' (trapped).

     2.  If 'Last of' is not used, then it doesn't really matter what the value of @Boundary is... the

         first day of the desired period will be returned.

     3.  If 'Last of' is used, then the last day of the desired period will be returned.

     @PeriodOffSet: Describes how many @Period to offset from @DateIN

     1.  Possible string values are:

         'This'     - Current @Period containing @DateIN

         'Current'  - Same as 'This'

         'Previous' - One @Period less than the @Period that contains @DateIn

         'Next'     - One @Period more than the @Period that contains @DateIn

     2.  Possible numeric values are:

         0  - Same as 'This' and 'Current' desicribed above

         <0 - Number of @Period less than the @Period that contains @DateIn (-1 same as 'Previous')

         >0 - Number of @Period more than the @Period that contains @DateIn (+1 same as 'Next')

         May be any number that will allow a legal date to be returned.

     @Period: The period type to use

     1.  Recommended possible string values are: (should be self-explanatory)

         'Week'    or anything that begins with "W"

         'Month'   or anything that begins with "M"

         'Quarter' or anything that begins with "Q"

         'Year'    or anything that begins with "Y"

     2.  If any other value is used other than words beginning with the above single letters,

         and error is forced (cannot raise error in a function so a non-date string is equated to a

         DATETIME to force an error)

     @DateIN: The date to include the period calculations of.

     1.  May be an instinsicly convertable string representation of any valid date.

     2.  May be a date calculation that returns a date

     3.  May be any legal date expression

        

     Example usage: (GETDATE used as the datetime in most of these examples but any date will do)

     --Return the first day of this (or current) month (both return identical)

     SELECT dbo.udf_GetSelectedDate('First day of','This','Month',GETDATE())

     SELECT dbo.udf_GetSelectedDate('First of',0,'Month',GETDATE())

     SELECT dbo.udf_GetSelectedDate('anything but last of',0,'Month',GETDATE())

     --Return the last day of next week (all return identical)

     SELECT dbo.udf_GetSelectedDate('Last of','Next','Week',GETDATE())

     SELECT dbo.udf_GetSelectedDate('Last of',1,'Week',GETDATE())

     SELECT dbo.udf_GetSelectedDate('Last of',1,'Week from now',GETDATE())

     --Return the first day of 3 months ago

     SELECT dbo.udf_GetSelectedDate('First of',-3,'Months',GETDATE())

     You get the idea

     Revision History:

     12/04/2005 - Jeff Moden --Initial creation

    **************************************************************************************************/

    --===== Define the function parameters

            (

            @Boundary     VARCHAR(10),

            @PeriodOffSet VARCHAR(10),

            @Period       VARCHAR(10),

            @DateIn       DATETIME

            )

    RETURNS DATETIME AS BEGIN

    --===== Declare local variables

    DECLARE @DateOUT    DATETIME

    DECLARE @LastOffSet SMALLINT

        SET @LastOffSet = 0

    --===== Digitize non-numeric period offsets using instrinsic datatype conversions

     SELECT @PeriodOffSet =

            CASE @PeriodOffSet

                 WHEN 'This'     THEN 0

                 WHEN 'Current'  THEN 0

                 WHEN 'Previous' THEN -1

                 WHEN 'Next'     THEN +1

                 ELSE @PeriodOffSet

            END

    --===== If the "Last of" anything is requested, modify the offsets to accomodate

         IF @Boundary = 'Last of'

            SELECT @PeriodOffSet = @PeriodOffSet + 1,

                   @LastOffSet   = -1

    --===== Calculate the desired date (1st letter of period used for @Period)

     SELECT @DateOut =

                CASE LEFT(@Period,1)

                     WHEN 'W' THEN DATEADD(wk,DATEDIFF(wk,0,@DateIN)+@PeriodOffSet,0)+@LastOffSet

                     WHEN 'M' THEN DATEADD(mm,DATEDIFF(mm,0,@DateIN)+@PeriodOffSet,0)+@LastOffSet

                     WHEN 'Q' THEN DATEADD(qq,DATEDIFF(qq,0,@DateIN)+@PeriodOffSet,0)+@LastOffSet

                     WHEN 'Y' THEN DATEADD(yy,DATEDIFF(yy,0,@DateIN)+@PeriodOffSet,0)+@LastOffSet

                     ELSE 'Error'

                END

    --===== Return scalar value and exit

     RETURN @DateOut

        END

    Please send beer... I already have enough pretzels.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    Nicely done - thanks for putting in the time to make the UDF more versatile!

    Regards,

    Harley

     

Viewing 8 posts - 1 through 7 (of 7 total)

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