Introduction:
Here I will explain how to write query to get or find first and last day of current month or previous month in SQL server.
Here I will explain how to write query to get or find first and last day of current month or previous month in SQL server.
Description:
In
previous articles I explained DateAdd function Example, Pass table as parameter to procedure, difference between function and stored
procedures,
joins in SQL Server, substring function in SQL server
and many articles relating to SQL
server.
Now I will explain query to get first and last day of current month in SQL
server.
Get First and Last Day of Current Month
If
you want to get First and last day of current month we need to use DATEADD function and write the query like this
SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),103) AS [Date],
'First Day of Current Month' AS [Type]
UNION
SELECT CONVERT(VARCHAR(10),GETDATE(),103) AS Date_Value, 'Today' AS [Type]
UNION
SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),103) AS [Date],
'Last Day of
Current Month' AS [Type]
|
Output:
Date
|
Type
|
1/10/2012
|
First Day of Current Month
|
30/10/2012
|
Today
|
31/10/2012
|
Last Day of Current Month
|
Get First and Last Day of Previous Month
If
you want to get First and last day of previous month we need to use DATEADD function and write the query like this
SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,-1,GETDATE()))-1),DATEADD(mm,-1,GETDATE())),103) AS [Date],
'First Day of Previous Month' AS [Type]
UNION
SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(GETDATE())),GETDATE()),103) AS [Date],
'Last Day of
Previous Month' AS [Type]
|
Output:
Date
|
Type
|
1/9/2012
|
First Day of Previous Month
|
30/09/2012
|
Last Day of Previous Month
|
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
|
|
8 comments :
hi suresh ji! you r doing great job.thanks for you articles.
sir,its my request to u ,please teach us MVC and WCF.
highly thankful to you
gd
very nice....sir.
gd
Hi sir,
I want output like this in sqlserver..
EmpId
----------
Emp001
Emp002
Emp003
-
-
-
-
-
Emp010
like this sir...
Your work is much valueable to me thank so much sir you know whatever problems that I was facing during my project your website has it all the solutions so I must say keep it on............
As always I say You are a true inspiration for me because you are simply great
Great job sir,its realy helpfull for me...keep doing..
we can use this also.
current month first day and last day:
-- First day and last day of current month
declare @dt datetime,@d1 datetime,@d2 datetime
declare @i int
select @dt=getdate()
select @i=day(getdate())-1
select @d1=dateadd(dd,-@i,@dt)
select @d2=dateadd(mm,1,@d1)-1
select @d1,@d2
previous month first day and last day:
declare @dt datetime,@d1 datetime,@d2 datetime
declare @i int
select @dt=getdate()
select @i=day(getdate())-1
select @d1=dateadd(mm,-1,dateadd(dd,-@i,@dt) )
select @d2=dateadd(mm,1,@d1)-1
select @d1,@d2