SQL Query to find the last day of the month

Question

I need to find the last day of a month in the following format    2013-05-31 00 00 00 000    Anybody please help out

User · Accepted Answer

Try this one -  CREATE FUNCTION  dbo   udf GetLastDayOfMonth          Date DATETIME   RETURNS DATETIME AS BEGIN      RETURN DATEADD d  -1  DATEADD m  DATEDIFF m  0   Date    1  0    END   Query   DECLARE  date DATETIME SELECT  date    2013-05-31 15 04 10 027   SELECT DATEADD d  -1  DATEADD m  DATEDIFF m  0   date    1  0     Output   ----------------------- 2013-05-31 00 00 00 000

User · Answer

In Snowflake  and likely other SQL engines   you can use LAST DAY  select to date  2015-05-08T23 39 20 123-07 00   as  quot DATE quot          last day  quot DATE quot    MONTH   as  quot LAST DAY OF MONTH quot    Which returns  DATE         LAST DAY OF MONTH 2015-05-08          2015-05-31

User · Answer

declare  date date getdate   declare  st date date  end dt date set  st date convert varchar 5  year  date    -  convert varchar 5  month  date    -01  set  end dt DATEADD day -1  DATEADD month 1  st date   ---------              -------------- select  st date as  START DATE   end dt AS  END DATE

User · Answer

Calculate the last date of the month is quite simple calculation - 1 - Find the total months count till today s date using DATEDIFF function - Select DATEDIFF MM 0 GETDATE     Output - 1374  If getdate   output is  quot 2014-07-23 19 33 46 850 quot  2 -Increment by 1 into total months count - Select DATEDIFF MM 0 GETDATE    1  Output - 1375  If getdate   output is  quot 2014-07-23 19 33 46 850 quot  3 - Get the first date of next month - Select DATEADD MM DATEDIFF MM 0 GETDATE    1 0   Output -  2014-08-01 00 00 00 000   If getdate   output is  quot 2014-07-23 19 33 46 850 quot  4 - Subtract by -1 into the first date of next month  which will return last date of the current month - Select DATEADD DD -1 DATEADD MM DATEDIFF MM 0 GETDATE    1 0    Output -  2014-07-31 00 00 00 000   If getdate   output is  quot 2014-07-23 19 33 46 850 quot  In the same manner of calculation we can achieve the -  Last date of next month Last date of the previous month and so on

User · Answer

An excelent approach by me  Regards  DECLARE  MAXDATE INT  SELECT MAX DATEPART YEAR ORDERDATE   FROM Orders  DECLARE  MINDATE INT  SELECT MIN DATEPART YEAR ORDERDATE   FROM Orders  DECLARE  HORA INT  SELECT MIN  DATEPART HOUR ORDERDATE   FROM ORDERS  DECLARE  DIA INT   28  SELECT Employees EmployeeID   Orders  OrderID   OrderDate  FROM Employees INNER JOIN Orders  ON Employees EmployeeID   Orders EmployeeID Where   DATEPART YEAR ORDERDATE    gt   mindate and   DATEPART YEAR ORDERDATE   lt    maxdate and DATEPART HOUR ORDERDATE   HORA   and DATEPART DAY ORDERDATE  IN  30 31  OR DATEADD DAY 0 DATEPART DAY ORDERDATE   28 AND DATEADD MONTH 0 DATEPART MONTH ORDERDATE   2 ORDER BY 1 ASC

User · Answer

Just a different version of adding a month and subtracting a day for creating reports   ex  StartofMonth is  2019-10-01   dateadd day -1 dateadd month 1 StartofMonth     EndOfMonth will become  2019-10-31

User · Answer

select DATEADD MONTH  DATEDIFF MONTH  -1  GETDATE   -0  -1  LastDate

User · Answer

declare  date datetime  set  date   getdate    -- or some date select dateadd month 1 datediff month 0  date  -1

User · Answer

SQL Server 2012 introduces the eomonth function   select eomonth  2013-05-31 00 00 00 000   -- gt  2013-05-31

User · Answer

Select DATEADD DAY  - DAY DATEADD MONTH  1  GETDATE      DATEADD MONTH  1  GETDATE       This works great in T-sql      Replace the GETDATE   of the query with your column name

User · Answer

TO FIND 1ST and Last day of the Previous  Current and Next Month in Oracle SQL ----------------------------------------------------------------------------- SELECT  SYSDATE  LAST DAY ADD MONTHS SYSDATE -2   1 FDPM  LAST DAY ADD MONTHS SYSDATE -1   LDPM  LAST DAY ADD MONTHS SYSDATE -1   1 FDCM  LAST DAY SYSDATE LDCM  LAST DAY SYSDATE  1 FDNM  LAST DAY LAST DAY SYSDATE  1  LDNM FROM DUAL

User · Answer

dateadd month 1 datediff month 0 getdate    -1    To check run   print dateadd month 1 datediff month 0  date  -1

User · Answer

I know this question was for SQL Server 2005  but I thought I d mention- as of SQL 2012  there now is an EOMONTH   function that gets the last day of the month  To get it in the format specified by the original asker you d have to cast to a datetime   SELECT CAST eomonth GETDATE    AS datetime

User · Answer

Declare  GivenDate datetime  Declare  ResultDate datetime  DEclare  EOMDate datetime  Declare  Day int  set  GivenDate getdate    set  GivenDate   dateadd mm 1  GivenDate    set  Day  day  GivenDate   set  ResultDate dateadd dd - Day 1  GivenDate   select  EOMDate  dateadd dd -1   ResultDate   select  EOMDate

[sql-server] SQL Query to find the last day of the month

Examples related to sql-server

Examples related to sql-server-2005

Examples related to date

Examples related to datetime