To get first day of month using T-SQL based on the today's date use the following T-SQL statement:
Select Dateadd(d,1-DATEPART(d,getdate()),GETDATE()) as [First Day of the Month]
In the above sql statement we used getdate() function to get current date. We have also used datepart functin with d argument and today's date that returns day of the month. Now that we have these arguments we used DateAdd function and substracted from today's date numbers of days (eg if we have 16th of May 2011 we subctract from this date 16 days) and the trick is to add one day to get first day of month.
If you are interested in similar examples see our T-SQL First day of previous month
Hope that helps!
Katie
|
Share:
Comments Add Comment
Name:
Imtiyaz
Comment:
This is also working...:)
select DATEADD(dd,-DAY(getdate())+1, getdate()) -- First day of current month
select DATEADD(dd,-DAY(getdate()),DATEADD(mm, 1,getdate())) -- Last day of current month
select DATEADD(dd,-DAY(getdate())+1,DATEADD(mm,-1,getdate())) -- First day of previous month
select dateadd(mm,-1,DATEADD(dd,-DAY(getdate()),DATEADD(mm,1,getdate()))) -- Last day of previous month
Date Posted:
20/03/2012 11:52:42 AM
Name:
Emil
Comment:
Hi Imtiyaz
I'm afraid 3 out 4 of your queries contain bugs. Run scripts below with date I chose and see if it gives correct result.
declare @Date as date = '2012-02-29'
select dateadd(mm,-1,DATEADD(dd,-DAY(@Date),DATEADD(mm,1,@Date))) -- Last day of previous month
set @Date = '2012-03-31'
select DATEADD(dd,-DAY(@Date),DATEADD(mm, 1,@Date)) -- Last day of current month
select DATEADD(dd,-DAY(@Date)+1,DATEADD(mm,-1,@Date)) -- First day of previous month
Date Posted:
24/03/2012 9:05:28 AM
Name:
inder
Comment:
there is a function in oracle called trunc(date, month) which will give first day of current month
else
last_day(date) will give last day of current month and then add month (-1) can give last day of previou month and add_day(1) will give first day of current month
Date Posted:
12/10/2012 4:49:56 AM
Share your thoughts, questions and suggest improvements:
Add Comment
Consulting & Training Company
