first & last day of the month - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

first & last day of the month

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-19 : 07:47:51

Hello everyone.

I have a stored procedure that i want to fire off at the beginning of every month. But want to query a few tables based on the 1st day of month - the last day of month. But for the month before the present one. For example. 1/oct/2010 - 31/oct/2010 as we are in Nov. I have a variable set up already. as below.

declare @month datetime
SELECT @month = ( CASE WHEN DATEPART(m, getdate()) in (4, 6, 9, 11) THEN (GETDATE()-29)
when DATEPART(m, getdate()) =2 THEN (GETDATE()-27) else
(GETDATE()-30) END )

THis one searches from the beginning of the month depending on what month we are in. Ie subtracting 28, 30 or 31 days. But is there a better way in acheiving this.?

Kind Regards

Rob

would i be on the right lines doing it this way.

MCTS / MCITP certified

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 07:50:57

begining of previos month
select convert(datetime,convert(varchar(6),dateadd(mm,-1,getdate()),112) + '01')
end of previous month
select dateadd(dd,-1,convert(varchar(6),getdate(),112) + '01')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-19 : 07:51:03

SELECT DATEADD(month,datediff(month,0,getdate())-1,0),DATEADD(month,datediff(month,0,getdate()),0)-1

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-19 : 08:17:10

Could someone disect the above code and explain each bit,

I want to get my head round how it all works please

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-19 : 08:41:21

quote:
Originally posted by masterdineen

Could someone disect the above code and explain each bit,

I want to get my head round how it all works please

--SELECT DATEADD(month,datediff(month,0,getdate())-1,0)

select dateadd(day,0,0)
--Returns the base date as 1900-01-01 00:00:00.000

Select datediff(month,0,getdate())
Returns 1330 which is the number of month difference from base date

select datediff(month,0,getdate())-1
--Returns value for oct 2010 month i.e.,1329

select dateadd(month,1329,0)
--Returns first date of oct 2010, month 2010-10-01 00:00:00.000

-The above explanation is for first part. I feel that now you can make out yourself for second part.

Madhivanam : Please correct me if my explanation is wrong.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-19 : 09:01:07

thank you very much for all your help.

Much Appreciated

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-26 : 03:42:42

quote:
Originally posted by pk_bohra

quote:
Originally posted by masterdineen

Could someone disect the above code and explain each bit,

I want to get my head round how it all works please

--SELECT DATEADD(month,datediff(month,0,getdate())-1,0)

select dateadd(day,0,0)
--Returns the base date as 1900-01-01 00:00:00.000

Select datediff(month,0,getdate())
Returns 1330 which is the number of month difference from base date

select datediff(month,0,getdate())-1
--Returns value for oct 2010 month i.e.,1329

select dateadd(month,1329,0)
--Returns first date of oct 2010, month 2010-10-01 00:00:00.000

-The above explanation is for first part. I feel that now you can make out yourself for second part.

Madhivanam : Please correct me if my explanation is wrong.

You are correct

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources