Get the last day of the month in SQL

Question

I need to get the last day of the month given as a date in SQL  If I have the first day of the month  I can do something like this   DATEADD DAY  DATEADD MONTH  2009-05-01  1   -1    But does anyone know how to generalize it so I can find the last day of the month for any given date

User · Answer

WinSQL  I wanted to return all records for last month   where DATE01 between dateadd month -1 dateadd day 1 dateadd day -day today    today      and dateadd day -day today    today      This does the same thing   where month DATE01    month dateadd month -1 today     and year DATE01    year dateadd month -1 today

User · Answer

Try to run the following query  it will give you everything you want     Declare  a date  dateadd mm  Datediff mm 0 getdate    0  Print  First day of Current Month    Print  a  Print     set  a   dateadd mm  Datediff mm 0 getdate    1 -1  Print  Last day of Current Month    Print  a  Print      Print  First day of Last Month    set  a   dateadd mm  Datediff mm 0 getdate   -1 0  Print  a  Print      Print  Last day of Last Month     set  a   dateadd mm  Datediff mm 0 getdate    -1  Print  a  Print       Print  First day of Current Week    set  a   dateadd ww  Datediff ww 0 getdate    0  Print  a  Print      Print  Last day of Current Week    set  a   dateadd ww  Datediff ww 0 getdate    1 -1  Print  a  Print      Print  First day of Last Week    set  a    dateadd ww  Datediff ww 0 getdate   -1 0  Print  a  Print      Print  Last day of Last Week    set  a    dateadd ww  Datediff ww 0 getdate    -1  Print  a

User · Answer

using sql server 2005  this works for me   select dateadd dd -1 dateadd mm datediff mm 0 YOUR DATE  1 0     Basically  you get the number of months from the beginning of  SQL Server  time for YOUR DATE  Then add one to it to get the sequence number of the next month  Then you add this number of months to 0 to get a date that is the first day of the next month  From this you then subtract a day to get to the last day of YOUR DATE

User · Answer

WinSQL to get last day of last month  i e today is 2017-02-09  returns 2017-01-31   Select dateadd day -day today    today

User · Answer

Based on the most voted answer at below link I came up with the following solution   declare   mydate date   2020-11-09     SELECT DATEADD month  DATEDIFF month  0   mydate  1  -1  AS lastOfMonth  link  How can I select the first day of a month in SQL

User · Answer

This works for me  using Microsoft SQL Server 2005   DATEADD d -1 DATEADD mm  DATEDIFF m 0  2009-05-01   1 0

User · Answer

For SQL server 2012 or above use EOMONTH to get the last date of month   SQL query to display end date of current month  DECLARE  currentDate DATE   GETDATE   SELECT EOMONTH   currentDate  AS CurrentMonthED   SQL query to display end date of Next month  DECLARE  currentDate DATE   GETDATE   SELECT EOMONTH   currentDate  1   AS NextMonthED

User · Answer

Works in SQL server  Declare  GivenDate datetime SET  GivenDate   GETDATE    Select DATEADD MM DATEDIFF MM  0   GivenDate  0  --First day of the month   Select DATEADD MM DATEDIFF MM  -1   GivenDate  -1  --Last day of the month

User · Answer

This query can also be used   DECLARE  SelectedDate DATE    GETDATE    SELECT DATEADD DAY  - DAY  SelectedDate   DATEADD MONTH  1    SelectedDate   EndOfMonth

User · Answer

Using SQL Server  here is another way to find last day of month     SELECT DATEADD MONTH 1 GETDATE   - day DATEADD MONTH 1 GETDATE

User · Answer

You could get the days in the date by using the DAY   function    dateadd day  -1  dateadd month  1  dateadd day  1 - day date   date

User · Answer

I know this is a old question but here is another solution that works for me   SET  dtDate    your date  DATEADD s -1 DATEADD mm  DATEDIFF m 0  dtDate  1 0     And if some one is looking for different examples here is a link http   blog sqlauthority com 2007 08 18 sql-server-find-last-day-of-any-month-current-previous-next   I hope this helps some one else  stackoverflow Rocks

User · Answer

Just extend your formula out a little bit   dateadd day  -1      dateadd month  1          cast month  5 15 2009   as varchar 2                1              cast year  5 15 2009   as varchar 4

User · Answer

Here s my version  No string manipulation or casting required  just one call each to the DATEADD  YEAR and MONTH functions   DECLARE  test DATETIME SET  test   GETDATE    -- or any other date  SELECT DATEADD month    YEAR  test  - 1900    12    MONTH  test   -1

User · Answer

---Start End of previous Month  Declare  StartDate datetime   EndDate datetime  set  StartDate   DATEADD month  DATEDIFF month  0  GETDATE   -1 0   set  EndDate   EOMONTH  DATEADD month  DATEDIFF month  0  GETDATE   -1 0     SELECT  StartDate  EndDate

User · Answer

I wrote following function  it works   It returns datetime data type  Zero hour  minute  second  miliseconds    CREATE Function  dbo   fn GetLastDate         date datetime   returns datetime as begin  declare  result datetime   select  result   CHOOSE month  date      DATEADD DAY  31 -day  date    date    IIF YEAR  date    4   0  DATEADD DAY  29 -day  date    date   DATEADD DAY  28 -day  date    date      DATEADD DAY  31 -day  date    date     DATEADD DAY  30 -day  date    date     DATEADD DAY  31 -day  date    date    DATEADD DAY  30 -day  date    date     DATEADD DAY  31 -day  date    date    DATEADD DAY  31 -day  date    date    DATEADD DAY  30 -day  date    date    DATEADD DAY  31 -day  date    date    DATEADD DAY  30 -day  date    date     DATEADD DAY  31 -day  date    date     return convert date   result   end   It s very easy to use   2 example   select  dbo   fn GetLastDate   2016-02-03 12 34 12    select  dbo   fn GetLastDate  GETDATE

User · Answer

My 2 cents   select DATEADD DAY -1 DATEADD MONTH 1 DATEADD day  0- DATEPART dd  2008-02-12  -1    2008-02-12       Raj

User · Answer

Take some base date which is the 31st of some month e g   20011231   Then use the following procedure  I have given 3 identical examples below  only the  dt value differs    declare  dt datetime   set  dt    20140312   SELECT DATEADD month  DATEDIFF month   20011231    dt    20011231       set  dt    20140208   SELECT DATEADD month  DATEDIFF month   20011231    dt    20011231       set  dt    20140405   SELECT DATEADD month  DATEDIFF month   20011231    dt    20011231

User · Answer

Based on the statements    SELECT DATEADD MONTH  1   x            -- Add a month to the supplied date  x   and  SELECT DATEADD DAY   0 - DAY  x    x   -- Get last day of month previous to the supplied date  x   how about adding a month to date  x and then retrieving the last day of the month previous to that  i e  The last day of the month of the supplied date   DECLARE  x  DATE    20-Feb-2012   SELECT DAY DATEADD DAY   0 - DAY DATEADD MONTH  1   x    DATEADD MONTH  1   x      Note  This was test using SQL Server 2008 R2

User · Answer

From SQL Server 2012 you can use the EOMONTH function      Returns the last day of the month that contains the specified date    with an optional offset    Syntax  EOMONTH   start date    month to add           How     I can find the last day of the month for any given date    SELECT EOMONTH  SomeGivenDate

[sql] Get the last day of the month in SQL

Examples related to sql

Examples related to sql-server

Examples related to tsql