What is this saying ?

  • I believe this is saying Last day of previous month and first day of current month ? Can someone please confirm ?

    Steve

    DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))

    and

    DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))

  • steve4134 (4/29/2013)


    I believe this is saying Last day of previous month and first day of current month ? Can someone please confirm ?

    Steve

    DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))

    and

    DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))

    run this in SSMS

    select DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))

    select DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • good link here

    http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • From the original post:

    select

    GETDATE() CurrentDate,

    DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) FirstOfPreviousMonth,

    DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101)) [LastOfPreviousMonth-NOT]

    Results:

    CurrentDate FirstOfPreviousMonth LastOfPreviousMonth-NOT

    ----------------------- ----------------------- -----------------------

    2013-04-29 12:58:22.217 2013-03-01 00:00:00.000 2013-03-30 00:00:00.000

    What you probably want:

    select

    getdate() CurrentDate,

    dateadd(month, datediff(month, 0, getdate()) - 1, 0) FirstOfPreviousMonth,

    dateadd(month, datediff(month, 0, getdate()), -1) LastOfPreviousMonth

    Results:

    CurrentDate FirstOfPreviousMonth LastOfPreviousMonth

    ----------------------- ----------------------- -----------------------

    2013-04-29 12:58:22.217 2013-03-01 00:00:00.000 2013-03-31 00:00:00.000

    And if you are generating dates to bracket a month for a WHERE clause I would use the following:

    select

    getdate() CurrentDate,

    dateadd(month, datediff(month, 0, getdate()) - 1, 0) FirstOfPreviousMonth,

    dateadd(month, datediff(month, 0, getdate()), 0) FirstOfCurrentMonth

    And use it this way:

    ...

    WHERE

    MyDateCol >= FirstOfPreviousMonth and

    MyDateCol < FirstOfCurrentMonth

  • I can not express the joy that comes to me when I see a community come out and help someone so quick. I thank you again. I was correct in my assumptions last month first start date to last date of last month. Thanks again

    steve

  • steve4134 (4/29/2013)


    I can not express the joy that comes to me when I see a community come out and help someone so quick. I thank you again. I was correct in my assumptions last month first start date to last date of last month. Thanks again

    steve

    You did look at the results of the code you posted, right? What you posted returned 3/1/2013 and 3/30/2013 using today (4/29/2013). Not the first and last day of the previous month.

    If that is what you need, look at the code I provided.

  • Yep Lynn

    I just wanted to confirm that what I had wrote was indeed from last month threw the end of the month ran today. So if I ran this in May it would be April 1st to last day of April.

    Thanks again

    steve

  • steve4134 (4/29/2013)


    Yep Lynn

    I just wanted to confirm that what I had wrote was indeed from last month threw the end of the month ran today. So if I ran this in May it would be April 1st to last day of April.

    Thanks again

    steve

    Just to be sure you actually understand, the code you posted will NOT always give you the last day of the previous month.

    If you don't believe me, try it yourself with various dates using the following code changing the value for the variable @ThisDate:

    declare @ThisDate datetime;

    set @ThisDate = '20130430 14:25';

    select

    @ThisDate CurrentDate,

    DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@ThisDate)-1),@ThisDate),101)) FirstOfPreviousMonth,

    DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@ThisDate))),DATEADD(mm,1,@ThisDate)),101)) [LastOfPreviousMonth-NOT]

    Modified for SQL Server 2005.

  • Also, if the column(s) you are testing the dates against have time values other than 00:00:00.000, you will miss data at the end of the month.

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

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