Last day of Month

  • Finding Dates like first day and last day etc

    ----Today

    SELECT GETDATE() 'Today'

    ----Yesterday

    SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'

    ----First Day of Current Week

    SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'

    ----Last Day of Current Week

    SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'

    ----First Day of Last Week

    SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'

    ----Last Day of Last Week

    SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'

    ----First Day of Current Month

    SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'

    ----Last Day of Current Month

    SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'

    ----First Day of Last Month

    SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'

    ----Last Day of Last Month

    SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

    ----First Day of Current Year

    SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'

    ----Last Day of Current Year

    SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'

    ----First Day of Last Year

    SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'

    ----Last Day of Last Year

    SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'

  • praneshram (12/14/2011)


    Finding Dates like first day and last day etc

    ...

    Using DATEADD(ms,-3,expression) is sooooo SQL Server 2005 😉

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • if you upgrade to new SQL 2012 you can use EOMONTH()

  • praneshram (12/14/2011)


    ----Last Day of Last Month

    SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

    Gosh no. Always find the first day of the next month and use "less than" instead of "between" to make your code "bullet proof".

    --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 (12/14/2011)


    praneshram (12/14/2011)


    ----Last Day of Last Month

    SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

    Gosh no. Always find the first day of the next month and use "less than" instead of "between" to make your code "bullet proof".

    And use SYSDATETIME() instead of GETDATE() to get rid of that silly 3ms truncation.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (12/14/2011)


    Jeff Moden (12/14/2011)


    praneshram (12/14/2011)


    ----Last Day of Last Month

    SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

    Gosh no. Always find the first day of the next month and use "less than" instead of "between" to make your code "bullet proof".

    And use SYSDATETIME() instead of GETDATE() to get rid of that silly 3ms truncation.

    Nah... use whole dates for criteria and you don't have to worry about it. 🙂

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

  • pls try below code

    declare @Year int

    declare @mon int

    declare @Main datetime

    set @year=2012

    set @mon=3

    set @Main=convert(datetime,conert(varchar(10),@year)+convert(varchar(10),@mon)+'01')

    select convert(varchar(10),dateadd(ss,-1,dateadd(mm,1,@Main)),120)

  • declare @Year int

    declare @mon int

    declare @Main datetime

    set @year=2012

    set @mon=3

    set @Main=convert(datetime,conert(varchar(10),@year)+'-'+convert(varchar(10),@mon)+'-'+'01')

    select convert(varchar(10),dateadd(ss,-1,dateadd(mm,1,@Main)),120)

  • subbareddy542 (12/17/2011)


    declare @Year int

    declare @mon int

    declare @Main datetime

    set @year=2012

    set @mon=3

    set @Main=convert(datetime,conert(varchar(10),@year)+'-'+convert(varchar(10),@mon)+'-'+'01')

    select convert(varchar(10),dateadd(ss,-1,dateadd(mm,1,@Main)),120)

    Your code will produce an error, syntax error on conert(varchar(10)

    Modified code:

    declare @Year int

    declare @mon int

    declare @Main datetime

    set @year=2012

    set @mon=3

    set @Main=convert(datetime,convert(varchar(10),@year)+'-'+ convert(varchar(10),@mon)+'-'+'01')

    select convert(varchar(10),dateadd(ss,-1,dateadd(mm,1,@Main)),120)

    But in most practical case you would not know the year or the month and you would need to calculate form the current date or a date in a table, i.e. OrderDate etc

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • declare @y int

    declare @m int

    select @y=2012,@m=2

    select dateadd(month,(@y-1900)*12+@m,0)-1

  • VIG (12/18/2011)


    declare @y int

    declare @m int

    select @y=2012,@m=2

    select dateadd(month,(@y-1900)*12+@m,0)-1

    Because there's no string conversions in that, that's one of the fastest was to do this... nicely done.

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

  • Just thought I'd throw these in... they get rid of the behind-the-scenes operation of parenthetical grouping and remove one extra subtraction (for the month end calculation) for a tiny bit more speed (it would a fair number of rows to see a real difference). The "22800" number is 1900*12. The 22801 is similar and isn't a mistake. It gives one month less in the subtraction to find the first day of the month.

    DECLARE @Year INT, @Month INT;

    SELECT @Year = 2012, @Month = 2;

    SELECT FirstDayOfMonth = DATEADD(MONTH,@Year*12-22801+@Month, 0),

    LastDayOfMonth = DATEADD(MONTH,@Year*12-22800+@Month,-1)

    ;

    --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 (12/19/2011)


    Just thought I'd throw these in... they get rid of the behind-the-scenes operation of parenthetical grouping and remove one extra subtraction (for the month end calculation) for a tiny bit more speed (it would a fair number of rows to see a real difference). The "22800" number is 1900*12. The 22801 is similar and isn't a mistake. It gives one month less in the subtraction to find the first day of the month.

    DECLARE @Year INT, @Month INT;

    SELECT @Year = 2012, @Month = 2;

    SELECT FirstDayOfMonth = DATEADD(MONTH,@Year*12-22801+@Month, 0),

    LastDayOfMonth = DATEADD(MONTH,@Year*12-22800+@Month,-1)

    ;

    Cool ! But I do not like such solutions with "magic" numbers. It's a little bit faster, but more unclear

  • VIG (12/19/2011)


    Jeff Moden (12/19/2011)


    Just thought I'd throw these in... they get rid of the behind-the-scenes operation of parenthetical grouping and remove one extra subtraction (for the month end calculation) for a tiny bit more speed (it would a fair number of rows to see a real difference). The "22800" number is 1900*12. The 22801 is similar and isn't a mistake. It gives one month less in the subtraction to find the first day of the month.

    DECLARE @Year INT, @Month INT;

    SELECT @Year = 2012, @Month = 2;

    SELECT FirstDayOfMonth = DATEADD(MONTH,@Year*12-22801+@Month, 0),

    LastDayOfMonth = DATEADD(MONTH,@Year*12-22800+@Month,-1)

    ;

    Cool ! But I do not like such solutions with "magic" numbers. It's a little bit faster, but more unclear

    Absolutely understood on the "magic" numbers thing. Thanks for the feedback, Vig.

    --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 (12/19/2011)


    VIG (12/19/2011)


    Jeff Moden (12/19/2011)


    Just thought I'd throw these in... they get rid of the behind-the-scenes operation of parenthetical grouping and remove one extra subtraction (for the month end calculation) for a tiny bit more speed (it would a fair number of rows to see a real difference). The "22800" number is 1900*12. The 22801 is similar and isn't a mistake. It gives one month less in the subtraction to find the first day of the month.

    DECLARE @Year INT, @Month INT;

    SELECT @Year = 2012, @Month = 2;

    SELECT FirstDayOfMonth = DATEADD(MONTH,@Year*12-22801+@Month, 0),

    LastDayOfMonth = DATEADD(MONTH,@Year*12-22800+@Month,-1)

    ;

    Cool ! But I do not like such solutions with "magic" numbers. It's a little bit faster, but more unclear

    Absolutely understood on the "magic" numbers thing. Thanks for the feedback, Vig.

    And that's why they invented comment lines, to explain the magic numbers 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 31 through 44 (of 44 total)

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