Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Query to Find First and Last Day of Current Month or Previous Month

Oct 30, 2012
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.
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 RSS subscribe by email Subscribe by Email

11 comments :

Unknown said...

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

Anonymous said...

gd

munikumar said...

very nice....sir.

Anonymous said...

gd

Anonymous said...

Hi sir,

I want output like this in sqlserver..

EmpId
----------
Emp001
Emp002
Emp003
-
-
-
-
-
Emp010

like this sir...

Abhinavsingh993 said...

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

Unknown said...

Great job sir,its realy helpfull for me...keep doing..

Unknown said...

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

sadboy said...

i wants to pass the month and year, then i would get the first and last date of month for the corresponding year..

Unknown said...

Sir
I need to compare the current date,month and year with the Expired date(dd/mm/yy) entered in the database table and need the output of weather it is expired or not.I need sql query for this

Anonymous said...

nice...........

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.