First and Last Day Prior Month as Input Parameters

Question

First and Last Day Prior Month as Input Parameters

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 16, 2014 at 6:11 am

#285875

I have to create a report and I want all activity for the previous month.
I need to calculate the First and Last Day Prior Month to be used as Input Parameters.
Would something like this be the case or is there a better solution?
SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as FirstDayPreviousMonthWithTimeStamp,
DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as LastDayPreviousMonthWithTimeStamp
[/code]
I was thinking get the first day of the previous and current month to exclude the Timestamp and use a less then first day of current month?
I do not wasn't to miss anything.
Thanks.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Lynn has a some excellent date routing from his blog. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]

I would recommend not using the logic here for LastDayPreviousMonthWithtimeStamp. I assume you are using this in a where clause that will check for a date column less than that. Depending on the required accuracy you have a few milliseconds that will get missed. It would be far better to get the beginning of the current month and find anything less than that.

select dateadd(month, datediff(month, 0, GetDate()) - 1, 0) as BeginningOfLastMonth

,dateadd(month, datediff(month, 0, GETDATE()), 0) as BeginningOfThisMonth

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 2

That works, howeve, in the unlikely event there's stuff going on in the milliseconds portion of the datetime, I would opt for >= 1st day of the month and < 1st of next month

SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as FirstDayPreviousMonthWithTimeStamp,

DATEADD(month, DATEDIFF(month, 0, getdate()), 0) as LastDayPreviousMonthWithTimeStamp

This way you're absolutely certain you don't miss anything

2014-03-01 00:00:00.0002014-04-01 00:00:00.000

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

Welsh Corgi (4/16/2014)
I was thinking get the first day of the previous and current month to exclude the Timestamp and use a less then first day of current month?
I do not wasn't to miss anything.

That is absolutely the best and most bullet-proof way to do it even if a column supposedly won't ever have times in it or because it's a DATE column. It will survive changes to the underlying datatypes for the columns, new datatypes, etc, etc, without any code changes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)