QUERY TO FIND LAST DATE OF THE CURRENT MONTH / PREVIOUS MONTH / NEXT MONTH

-----QUERY TO FIND LAST DATE OF THE CURRENT MONTH / PREVIOUS MONTH / NEXT MONTH

USE ROHAN

GO

SELECT DATEADD(DAY,-DAY(DATEADD(MONTH,1,CONVERT(DATE,GETDATE()))),DATEADD(MONTH,1,CONVERT(DATE,GETDATE()))) AS'LAST DAY OF CURRENT MONTH',

DATEADD(DAY,-DATEPART(DAY,CONVERT(date,GETDATE())),CONVERT(DATE,GETDATE())) AS 'LAST DAY OF PREVIOUS MONTH',

DATEADD(DAY,-DATEPART(DAY,DATEADD(MONTH,2,CONVERT(DATE,GETDATE()))),DATEADD(MONTH,2,CONVERT(DATE,GETDATE()))) 'LAST DAY OF NEXT MONTH'

---- DYNAMIC SET THE DATED

DECLARE @SETDATE DATE = GETDATE()

SELECT DATEADD(DAY,-DAY(DATEADD(MONTH,1,CONVERT(DATE,@SETDATE))),DATEADD(MONTH,1,CONVERT(DATE,@SETDATE))) AS 'LAST DAY OF GIVEN CURRENT MONTH',

DATEADD(DAY,-DATEPART(DAY,CONVERT(date,@SETDATE)),CONVERT(DATE,@SETDATE)) AS 'LAST DAY OF GIVEN PREVIOUS MONTH',

DATEADD(DAY,-DATEPART(DAY,DATEADD(MONTH,2,CONVERT(DATE,@SETDATE))),DATEADD(MONTH,2,CONVERT(DATE,@SETDATE)))'LAST DAY OF GIVEN NEXT MONTH'

Subtracting x days from given date

USE Rohan

GO

CREATE TABLE DATEFIELD(ID INT,NAME VARCHAR(10),DATEFIELD DATE)

INSERT INTO DATEFIELD VALUES(1,'TERMIN','2015-12-19'),(2,'ABORT','2015-12-29'),(3,'USER','2015-12-24'),(4,'SHUTDOWN','2015-12-27')

Ex 1: Here dateadd subtracts 7 days from the current date.

EX 2 : Date Functions in WHERE Clause



SELECT * FROM DATEFIELD

WHERE DATEFIELD >= DATEADD(DAY,-7,CONVERT(DATE,GETDATE()))  AND DATEFIELD <=CONVERT(DATE,GETDATE())

Here dateadd subtracts 7 days from the current date.