Month Range Function - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Month Range Function

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-08-28 : 06:51:34

I want to get min & max of month days from the current date. Is there something for this?

Taking getdate() as input, eg a day in August, how do I get '2008-08-01' and '2008-08-31' out?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-28 : 06:54:21

SELECT DATEADD(m, DATEDIFF(m, 0, getdate()), 0), DATEADD(m, DATEDIFF(m, 0, getdate())+1, 0)-1

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-08-28 : 15:04:26

You're an absolute genius! How d'ya do that? I know you just picked it out from a archive somewhere. Can't pick any holes in it - it probably even works for leap years...

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-28 : 15:22:52

it's not that hard...go through it and see what it does

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-09-01 : 10:20:51

How do I add another field to those two to go back a month from today? Doesn't have to be spot-on. 30 days back would be good.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 10:29:00

Is this?
SELECT DATEADD(month, DATEDIFF(month, 0, getdate())-1, 0)

Madhivanan

Failing to plan is Planning to fail

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-09-02 : 04:55:46

I currently have the current script to get these four columns which is just perfect. Working in Crystal Reports. I need to zoom in to just one day and get the start and end of a day. Tried swapping the m for d and having no luck - just getting 00:00:00. When getting back 00:00:01 to 23:59:59 as a range I may want to adjust this to office hours... just wanna do what I got with months with days...

SELECT getdate() as today,
DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as first_day,
DATEADD(m, DATEDIFF(m, 0, getdate())+1, 0)-1 as last_day,
getdate()-30 as month_ago

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 05:10:38

[code]SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS Today,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FirstDay,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1) AS LastDay,
DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(MONTH, -1, GETDATE())), 0) AS MonthAgo[/code]It is often better to have[code]SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS Today,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FirstDay,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 31) AS FirstDayNextMonth,
DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(MONTH, -1, GETDATE())), 0) AS MonthAgo[/code]And write a query like this

SELECT * FROM Table1 WHERE Col1 >= @ThisMonth AND Col1 < @NextMonth

That way, you are guaranteed to include all times too.

Or use your first query and write

SELECT * FROM Table1 WHERE Col1 >= @ThisMonth AND Col1 < DATEADD(DAY, 1, @LastMonth)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources