Post reply

t-sql first last day date of the month

  • November 12, 2009 at 12:28 pm

    #213959

    Comments posted to this topic are about the item t-sql first last day date of the month

  • December 1, 2009 at 5:20 am

    #1085702

    Hi,

    I think the Last date can be font by

    SELECT @r=dateadd(day,-(datepart(day,@d)),dateadd(mm,1,@d))

    No need to use

    SELECT @r=dateadd(day,-(datepart(day,dateadd(mm,1,@d))),dateadd(mm,1,@d))

    Because, datepart(day,@d) and datepart(day,dateadd(mm,1,@d)) will return the same result. Then why to add one month to @d?

  • December 1, 2009 at 5:30 am

    #1085706

    A possible alternative is convert/cast with datetime styles (e.g. 112 for ISO in the form of yyyymmdd). This comes in often very handy for date calculations:

    declare @MyDate datetime

    set @MyDate = getdate()

    -- first day of given month (yyyymm01)

    select convert(datetime, convert(varchar, (year(@MyDate) * 10000) + (month(@MyDate) * 100) + 1), 112)

    -- last day of given month (first day of next minus one day -> yyyymm01 + 1 month - 1 day)

    select dateadd(month, 1, convert(datetime, convert(varchar, (year(@MyDate) * 10000) + (month(@MyDate) * 100) + 1), 112)) - 1

    Cheers, R.

  • December 1, 2009 at 6:09 am

    #1085721

    For the first day of the month, why use the double negative -- why not just

    dateadd(day,1-datepart(day,@d),@d)

  • December 1, 2009 at 6:54 am

    #1085754

    So many ways to calculate dates.... 🙂

    within the context of the SP, how about only one select statement to give you both first and last?

    need to change @FL to 0 = First day and 1 = Last day

    DECLARE @FL INT, @d AS DATETIME

    SET @d = ISNULL(@d,GETDATE())

    SET @FL = 0-- First day of the month

    SELECT DATEADD(mm, @FL + DATEDIFF(mm, 0, @d), 0) - @FL

    SET @FL = 1-- Last day of the month

    SELECT DATEADD(mm, @FL + DATEDIFF(mm, 0, @d), 0) - @FL

    Arkware

  • December 1, 2009 at 1:10 pm

    #1086070

    The other question is why use more variables and code than needed?

    DECLARE @d SMALLDATETIME

    SELECT DATEADD(mm, DATEDIFF(mm, 0, ISNULL(@d, GETDATE())), 0)

    SELECT DATEADD(d, -1, DATEADD(mm, 1 + DATEDIFF(mm, 0, ISNULL(@d, GETDATE())), 0))

    As a side note, if you're going to do quite a bit of date manipulation you're better off using a calendar table. There are plenty of examples out there so I won't go into all of that here. What if you need the first and last date of every month in a period? You could use a numbers table and date functions but the calendar table is much more efficient.

    Just my two cents worth 😉

  • December 2, 2009 at 3:31 am

    #1086339

    Hello,

    if you want to take off hours, minutes and seconds, getting only the "date" part of the calculated date, add this line of code:

    SET @r = cast(floor(cast(@R as FLOAT)) as DATETIME)

    Best wishes,

    Francesc

  • December 2, 2009 at 6:01 am

    #1086403

    The code in my previous post will also strip off the time 😉

  • December 2, 2009 at 11:14 am

    #1086685

    These return different results for the end of months preceding a month that has more days in it. For instance January has 31 days, and February only 28 or 29 days.

    Here is an example:

    DECLARE @d datetime;

    SET @d = '01/29/2009';

    -- Last day of month

    SELECT

    dateadd(day,-(datepart(day,@d)),dateadd(mm,1,@d)),

    -- Returns 2009-01-30 00:00:00.000, incorrect

    dateadd(day,-(datepart(day,dateadd(mm,1,@d))),dateadd(mm,1,@d));

    -- Returns 2009-01-31 00:00:00.000, correct

  • May 23, 2016 at 7:02 am

    #1880732

    Thanks for the script.

Viewing 10 posts - 1 through 9 (of 9 total)

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