SQL Date Function

Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.


DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,'First Day of Next Month'


First Day of Next Month


DECLARE @datum DATETIME
SET @datum=GETDATE()

DECLARE @datFuture DATETIME
SET @datFuture = dateadd(mm,1,@datum)

DECLARE @dateFirstDayNewMonth datetime
SET @dateFirstDayNewMonth = cast(year(@datFuture) as varchar(50)) + '-' + cast(month(@datFuture) as varchar(50)) + '-1'

print @dateFirstDayNewMonth


OR


DECLARE @datum DATETIME
SET @datum=getdate()
select dateadd(month,datediff(month,0,@datum)+1,0)


print all the months between 2 dates, eg. 01 Aug 2006 and 31 March 2007


declare @date1 datetime,@date2 datetime
select @date1='01 Aug 2006', @date2 ='31 mar 2007'

select dateadd(month,number,@date1) from master..spt_values
where type='p' and number between 0 and datediff(month,@date1,@date2)


OR


DECLARE @date1 DATETIME,
@date2 DATETIME

SELECT @date1 = '01 Aug 2006', @date2 = '31 mar 2007';

WITH months(monthnames)
AS (SELECT @date1 AS monthnames
UNION ALL
SELECT DATEADD(mm,1,monthnames)
FROM months
WHERE monthnames < @date2) SELECT STUFF(CONVERT(VARCHAR(12),monthnames,106),1,3,'') AS monthname FROM months OPTION (MAXRECURSION 0) first date of last month


select cast(cast(datepart(month, dateadd(mm, -1, getdate())) as varchar) +
'/' +
cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))
as varchar) + '/' +
cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)


This week start date Monday


select DATEADD(wk, DATEDIFF(wk,0,getdate()),0)


This week enddate


select DATEADD(wk, DATEDIFF(wk,-7,getdate()),-1)


Last week start date


select DATEADD(wk, DATEDIFF(wk,0,getdate()),-7)


Last week end date


select DATEADD(wk, DATEDIFF(wk,0,getdate()),-1)


First day of this year


select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)


Last day of the prior year


select cast(floor(cast(dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as float)) as datetime)


First day of next year


select DATEADD(yy, DATEDIFF(yy,-1,getdate()), 0)


First day of prior year


SELECT DATEADD(yy, DATEDIFF(yy,0,dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))), 0)


This week last Year start date Monday


select cast(floor(cast(dateadd(wk,datediff(wk,0,dateadd(yy,-1,getdate()) ),0)as float)) as datetime)


This day last year


select dateadd(yy,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))


First day of the current month


select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)


Last day of the current month


select cast(floor(cast(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))as float)) as datetime)


First day of the current month Last year


select dateadd(yy,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))


Last day of the current month Last Year


select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)))as float)) as datetime)