Thursday, September 20, 2012

In this post I'm going to demonstrate how you can get first and last date of any month / given date.

Lets say we have a date '2012-02-15'.

Now I want first('2012-02-01') and last('2012-02-29') date for the same directly using sql query!
So here it starts:
Declare three variables :
Declare @dt datetime,@mindate datetime,@maxdate datetime;

Now Set value of @dt(Our initial date) :
set @dt='2012-02-15'

As we all know that 01 is always the first day of every month so the start-date must be :
set @mindate=convert(varchar,DATEPART(year,@dt))+'-'+convert(varchar,DATEPART(MONTH,@dt))+'-01';

*[Note: I have converted the datepart function's value to varchar because datepart always returns int and we want concatenation not addition :-) ]*

To get the last-date we gona do a small trick, add 1 month in @mindate and then subtract 1 day from the result !
set @maxdate=dateadd(day,-1,DATEADD(month,1,@mindate)) ;

So finally here is the result :
select @mindate as StartDate,@maxdate as EndDate

Output will be :

StartDate EndDate
2012-02-01 2012-02-29

You can also create a table valued function for same as:

create function GetMinMaxDays(@dt datetime)
returns @temp table(StartDate varchar(10),EndDate varchar(10))
declare @mindate datetime,@maxdate datetime;
set @mindate=convert(varchar,DATEPART(year,@dt))+'-'+convert(varchar,DATEPART(MONTH,@dt))+'-01';
set @maxdate=dateadd(day,-1,DATEADD(month,1,@mindate));
insert @temp
select convert(varchar(10),@mindate,105),convert(varchar(10),@maxdate,105);

 will convert datetime value to yyyy-MM-dd format string]*

Call above function as:
select * from GetMinMaxDays('2012-09-10')

Output will be :

StartDate EndDate
01-09-2012 30-09-2012


Post a Comment