Function to set start and end date @variables

  • ColdCoffee (4/4/2012)


    True, Jeff. Thats why in my code, i used -3 for milliseconds. OP posted this in 2008 forums, so i think its better to ask him to use DATETIME2..

    I'm a bit surprised by that answer, CC. I thought you knew to always use the next time period and use >= and < for date comparisons instead of trying to shoehorn in a time.

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

  • schillingt (4/4/2012)


    I had already dropped the miliseconds due to this issue. I agree that I should drop the time stamp however even though all our date fields are defined as DATETIME and the selection logic seemed to work the same both ways with and without the 23:59:59 appended.

    Thank you all for the help oin this.

    See Lynn's post for the correct way to handle date ranges regardless of the date datatype.

    --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 Moden (4/4/2012)


    ColdCoffee (4/4/2012)


    True, Jeff. Thats why in my code, i used -3 for milliseconds. OP posted this in 2008 forums, so i think its better to ask him to use DATETIME2..

    I'm a bit surprised by that answer, CC. I thought you knew to always use the next time period and use >= and < for date comparisons instead of trying to shoehorn in a time.

    Yes Jeff, i agree with u and Lynn, but OP specifically posted that he needed milliseconds, if you could see his code, he was CONVERTing to varchar and appending Milliseconds, so i thot he was really needing the milliseconds part. But still, i should also have made a note in my post, first place! Thanks for the pointers, Jeff and Lynn!

  • ColdCoffee (4/4/2012)


    Jeff Moden (4/4/2012)


    ColdCoffee (4/4/2012)


    True, Jeff. Thats why in my code, i used -3 for milliseconds. OP posted this in 2008 forums, so i think its better to ask him to use DATETIME2..

    I'm a bit surprised by that answer, CC. I thought you knew to always use the next time period and use >= and < for date comparisons instead of trying to shoehorn in a time.

    Yes Jeff, i agree with u and Lynn, but OP specifically posted that he needed milliseconds, if you could see his code, he was CONVERTing to varchar and appending Milliseconds, so i thot he was really needing the milliseconds part. But still, i should also have made a note in my post, first place! Thanks for the pointers, Jeff and Lynn!

    Understood. I always try to protect people that think they need milliseconds. 🙂 They just don't know what a mistake the ol' 23:59:59.xxx thing is and they don't understand the BETWEEN with the use of DATETIMEs is a form of "Death by SQL".

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

  • {edit} Had a bug in the comments (believe it or not) I posted. I'll be back.

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

  • I had a bug in the "usage" comments in the code I previously posted. I also took the time to add a usage comment very specific to this thread.

    schillingt (4/3/2012)


    Trying to put together a generic procedure or function that I can pass a date and some parametes to and have it return a start and end date that I can put into @StartDate and @EndDate for use in my select statements in a larger multi part procedure.

    With the understanding that you should almost always use the method that Lynn showed in his post for creating criteria for a range of dates or times, the following function should do it for you. As is normal with my code, the details for usage and a couple of other things are in the comments in the code.

    CREATE FUNCTION dbo.CreateDateTimeSpan

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

    Purpose:

    Given any legal DATETIME and a standard 2 character DATEPART, return the begining and next date/time of that DATEPART.

    When the DATEPART is WK (week), a day offset to determine the starting day for the week will be applied.

    Usage Examples:

    SELECT SpanType, StartDate, NextStartDate

    FROM dbo.CreateDateTimeSpan(@pDateTime, @pDatePart, @pDOWStart);

    SELECT st.selectlist, timespan.SpanType, timespan.StartDate, timespan.NextStartDate

    FROM dbo.sometable st

    CROSS APPLY dbo.CreateDateTimeSpan(st.somedatetimecolumn, @pDatePart, @pDOWStart) timespan;

    --===== Find the bounds of the current week starting on Sunday

    SELECT @StartDate = StartDate,

    @NextStartDate = NextStartDate

    FROM dbo.CreateDateTimeSpan(GETDATE(), 'wk', 7);

    Notes:

    1. The 3rd operand will only be used when @pDatePart = 'WK'and should be 1 for all other DATEPARTs.

    2. If an illegal date or datepart is given, the function will throw the following error.

    Msg 8115, Level 16, State 2, Line 7

    Arithmetic overflow error converting expression to data type datetime.

    3. Please see notes in code for legal DATEPARTs and values for @pDOWStart.

    4. This code does not rely on any DATEFIRST or language settings unless you're using the Hijri calendar.

    Since I've never used that calendar, I don't know how to code for it.

    Revision History:

    Rev 00 - 04 Apr 2012 - Jeff Moden - Initial creation

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

    --===== Declare the IO parameters

    (

    @pDateTime DATETIME,

    @pDatePart CHAR(2), --ss=Second, mi=Minute, hh=Hour, dd=Day, wk=Week, mm=Month, qq=Quarter, yy=Year

    @pDOWStart INT --1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    WITH cteOffset AS

    (--==== Calculate the reference date to use.

    -- For Week calculations, the @pDOWStart will offset the day.

    -- For time calculations, the current day will be used to prevent overflows.

    -- Won't affect other calculations until the last week of the year 9999.

    SELECT RefDate =

    CAST(

    CASE

    WHEN @pDatePart IN ('dd','wk','mm','qq','yy')

    THEN -53690+((@pDOWStart-1)%7) -- 1753-01-01 = -53690, a Monday

    WHEN @pDatePart IN ('hh','mi','ss')

    THEN DATEADD(dd,DATEDIFF(dd,0,@pDateTime),0)

    END

    AS DATETIME)

    )

    SELECT SpanType = @pDatePart,

    StartDate =

    CASE @pDatePart

    WHEN 'ss' THEN DATEADD(ss,DATEDIFF(ss,RefDate,@pDateTime),RefDate)

    WHEN 'mi' THEN DATEADD(mi,DATEDIFF(mi,RefDate,@pDateTime),RefDate)

    WHEN 'hh' THEN DATEADD(hh,DATEDIFF(hh,RefDate,@pDateTime),RefDate)

    WHEN 'dd' THEN DATEADD(dd,DATEDIFF(dd,RefDate,@pDateTime),RefDate)

    WHEN 'wk' THEN DATEADD(dd,DATEDIFF(dd,RefDate,@pDateTime)/7*7,RefDate)

    WHEN 'mm' THEN DATEADD(mm,DATEDIFF(mm,RefDate,@pDateTime),RefDate)

    WHEN 'qq' THEN DATEADD(qq,DATEDIFF(qq,RefDate,@pDateTime),RefDate)

    WHEN 'yy' THEN DATEADD(yy,DATEDIFF(yy,RefDate,@pDateTime),RefDate)

    ELSE CAST(-99999 AS DATETIME) --Throw an error if the datepart is wrong

    END,

    NextStartDate =

    CASE @pDatePart

    WHEN 'ss' THEN DATEADD(ss,DATEDIFF(ss,RefDate,@pDateTime)+1,RefDate)

    WHEN 'mi' THEN DATEADD(mi,DATEDIFF(mi,RefDate,@pDateTime)+1,RefDate)

    WHEN 'hh' THEN DATEADD(hh,DATEDIFF(hh,RefDate,@pDateTime)+1,RefDate)

    WHEN 'dd' THEN DATEADD(dd,DATEDIFF(dd,RefDate,@pDateTime)+1,RefDate)

    WHEN 'wk' THEN DATEADD(dd,DATEDIFF(dd,RefDate,@pDateTime)/7*7+7,RefDate)

    WHEN 'mm' THEN DATEADD(mm,DATEDIFF(mm,RefDate,@pDateTime)+1,RefDate)

    WHEN 'qq' THEN DATEADD(qq,DATEDIFF(qq,RefDate,@pDateTime)+1,RefDate)

    WHEN 'yy' THEN DATEADD(yy,DATEDIFF(yy,RefDate,@pDateTime)+1,RefDate)

    END

    FROM cteOffset

    ;

    The "/7*7" thing may look strange but it's just integer math for rounding the date down to the beginning of a week.

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

Viewing 6 posts - 16 through 20 (of 20 total)

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