SQL First and Last Day Of a Month

In This SQL Tutorial show you retrieve First Day and Last Day for a month using SQL Query.
It can dynamically to retrieve First Day and Last day for a month using SQL Query.

Example SQL Statement or Query below show you how to retrieve First Day or Last Day for a month. To display the date in different format, click here.

DECLARE @Today DATETIME
SELECT @Today = '6/17/2007'

Get First Day of a Month Using SQL Query
SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-3,@Today))
Value = 2007-03-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-2,@Today))
Value = 2007-04-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-1,@Today))
Value = 2007-05-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))
Value = 2007-06-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today))
Value = 2007-07-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,2,@Today))
Value = 2007-08-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,3,@Today))
Value = 2007-09-01 00:00:00.000


Get Last Day of a Month Using SQL Query
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-2,@Today))
Value = 2007-03-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-1,@Today))
Value = 2007-04-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,0,@Today))
Value = 2007-05-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,1,@Today))
Value = 2007-06-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,2,@Today))
Value = 2007-07-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,3,@Today))
Value = 2007-08-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,4,@Today))
Value = 2007-09-30 00:00:00.000

SQL DATEDIFF Function

Returns the number of date and time boundaries crossed between two dates

SQL DATEDIFF Syntax
DATEDIFF ( DatePart , StartDate , EndDate )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'

SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate
Return Value = 0 Year


SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate
Return Value = 1 quarter


SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate
Return Value = 2 Month


SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
Return Value = 61 day


SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
Return Value = 61 Day


SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate
Return Value = 9 Week


SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
Return Value = 1464 Hour


SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
Return Value = 87840 minute


SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
Return Value = 5270400 second


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate
Return Value = 86400000 millisecond

SQL DATEDIFF Function
SqlTutorials

SQL DATEADD Function

Returns a new datetime value based on adding an interval to the specified date.

SQL DATEADD Syntax
DATEADD ( datepart , number, date )


DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(Year, 3, @DateNow) AS NewDate
Return Value = 2010-06-04 00:00:00.000


SELECT DATEADD(quarter, 3, @DateNow) AS NewDate
Return Value = 2008-03-04 00:00:00.000


SELECT DATEADD(Month, 3, @DateNow) AS NewDate
Return Value = 2007-09-04 00:00:00.000


SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Week, 3, @DateNow) AS NewDate
Return Value = 2007-06-25 00:00:00.000


SELECT DATEADD(Hour, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 03:00:00.000


SELECT DATEADD(minute, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:03:00.000


SELECT DATEADD(second, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:03.000


SELECT DATEADD(millisecond, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:00.003