First and last day of each month for current year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

First and last day of each month for current year

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-08-01 : 23:23:16

How does one get the first and last day of each month for current year. Based on it could be any date within the year.

01-01-2013
31-01-2013
01-02-2013
28-02-2013
....
01-12-2013
31-12-2013

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-02 : 00:56:16

[code]

select getdate()
,DATEADD(mm,DATEDIFF(mm,0,getdate()),0) as [BoM]
,DATEADD(mm,DATEDIFF(mm,0,getdate())+1,-1) as [EoM]

[/code]

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 01:24:04

if 2012 there are other options too
EOMONTH() function can be used to get end of month date values
see

http://visakhm.blogspot.com/2013/06/t-sql-tips-beginning-of-month.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-08-02 : 01:40:00

Thanks stepson, I know how to do what you have suggested but this (I don't think) answers my question. In your example how do I get 1st Nov 2013 if the date was 5th Jan 2013 or the date was 6th Dec 2013 without changing the values in the code? The start dates could be hard coded as these do not change from year to year but the end dates do.

I have 2008.

This is the scenario,

Case 1:
Lets say it is the 1st of June 2013 and I would like to populate my start and end time filters with the month of February. I would need 1/2/2013 and 28/2/2013.

Case 2:
Lets say it is the 1st of November 2013 and I would like to populate my start and end time filters with the month of February. I would need 1/2/2013 and 28/2/2013.

I thought of doing something like this for the start date;

Select Convert(DateTime, [year] + '-01-01 00:00:00.000') as [1st of Jan] From(
Select Convert(varchar,DATEPART(YEAR,GETDATE())) as [year]) e

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 01:54:15

[code]
DECLARE @date datetime='20130105'

;With MonthDates
AS
(
SELECT DATEADD(yy,DATEDIFF(yy,0,@date),0) AS StartDate,DATEADD(mm,1,DATEADD(yy,DATEDIFF(yy,0,@date),0))-1 AS EndDate
UNION ALL
SELECT DATEADD(mm,1,StartDate),DATEADD(mm,1,EndDate)
FROM MonthDates
WHERE DATEADD(mm,1,EndDate)< DATEADD(yy,DATEDIFF(yy,0,@date)+1,0)
)
SELECT StartDate,EndDate
FROM MonthDates

OPTION (MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-02 : 02:02:47

[code]
declare @data1 as datetime='20131205'

set @data1 =DATEADD(yy,DATEDIFF(yy,0,@data1),0)

select
--@data1 ,number,
DATEADD(mm,DATEDIFF(mm,0,@data1)+number,0) as BoM
,DATEADD(mm,DATEDIFF(mm,0,@data1)+number+1,-1) as EoM
from master..spt_values
where type='P'
and number<=11

[/code]

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-02 : 02:29:48

base on visakh code , I change a bit ( i guess he is not in front a ssms console)


DECLARE @date datetime='20131205'

;with ListMonth
AS(
	select DATEADD(yy,DATEDIFF(yy,0,@date),0) as DataBoM
		   ,DATEADD(yy,DATEDIFF(yy,0,@date)+1,-1) as DataEoM
		   ,0 AS LVL
	union all
	select
		 DATEADD(mm,1,dataBom)
		 , DATEADD(dd,-1,DATEADD(mm,1,dataBom)) 
		 , lvl+1
	from ListMonth
	where DAtaBOM<=DATEADD(yy,DATEDIFF(yy,0,@date)+1,0)-1
)

select * from ListMonth
order by DataBoM

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-02 : 03:00:21

[code]DECLARE @CurrentDate DATETIME = GETDATE();

WITH cteDates(FirstOfMonth)
AS (
SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @CurrentDate), '19000101') AS FirstOfMonth

UNION ALL

SELECT DATEADD(MONTH, 1, FirstOfMonth) AS FirstOfMonth
FROM cteDates
WHERE DATEPART(MONTH, FirstOfMonth) < 12
)
SELECT FirstOfMonth,
DATEADD(DAY, -1, DATEADD(MONTH, 1, FirstOfMonth)) AS LastOfMonth
FROM cteDates;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-08-02 : 03:15:53

Thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 03:56:08

quote:

Originally posted by stepson

base on visakh code , I change a bit ( i guess he is not in front a ssms console)


DECLARE @date datetime='20131205'

;with ListMonth
AS(
	select DATEADD(yy,DATEDIFF(yy,0,@date),0) as DataBoM
		   ,DATEADD(yy,DATEDIFF(yy,0,@date)+1,-1) as DataEoM
		   ,0 AS LVL
	union all
	select
		 DATEADD(mm,1,dataBom)
		 , DATEADD(dd,-1,DATEADD(mm,1,dataBom)) 
		 , lvl+1
	from ListMonth
	where DAtaBOM<=DATEADD(yy,DATEDIFF(yy,0,@date)+1,0)-1
)

select * from ListMonth
order by DataBoM

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

I've edited it to correct the typos

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Angel6666
Starting Member

2 Posts

Posted - 2013-11-23 : 16:24:15

Hi,

I found something here...

http://sqlsaga.com/sql-server/how-to-find-the-first-day-of-the-current-month-and-last-day-of-the-month-in-sql-server/

https://www.facebook.com/sqlsaga

hope it helps :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources