SQL, how to get the date of previous year & last day of previous month

Hi Experts,

If getdate() : 11/19/2015
How can I get
1) Last Day of Previous Month: 10/31/2015 23:59:59
2) 12 month backwards: 11/1/2014

Thank you
tanj1035Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
SELECT DATEADD(MS, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) --1
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) --2

The basic form is:

DATEADD(<date_element>, DATEDIFF(<date_element>, 0, GETDATE()), 0)

Thus, if you wanted to get today's date, but stripped of less than a day, do this:
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

To get the current hour, stripped of less than an hour:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
>>"Last Day of Previous Month: 10/31/2015 23:59:59"

This indicates you are using BETWEEN

A far batter, more robust, and more easily implemented solution is to AVOID USING BETWEEN for date range selections.

Instead of going through hoops to arrive at the very last moment of the last day of any month, just go to the beginning point of the following month. Then by using less than that point in time you get the desired outcome with less complexity and less potential for error.

e.g.
where [date_column] >= "12 month backwards: 11/1/2014"
and  [datetime_column] <  "First Day of THIS Month i.e. 11/1/2015 00:00:00+00000"


where [date_column] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) --2
and  [datetime_column] <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) --1ish

Be careful about rounding errors. Suppose you're using the DATETIME data type for your column, and your data has both date and time values that aren't necessarily midnight. You want to filter a period of date and time data such as January 2012. Some people use the following filter form:

WHERE col BETWEEN '20120101' AND '20120131 23:59:59.999'
The problem is that 999 as the millisecond unit isn't a multiplication of the precision unit for DATETIME, which is three and a third milliseconds. Therefore, the value gets rounded to the next midnight, and your range might end up including rows it isn't supposed to include. Some people "fix" this problem by using 997 in the millisecond unit, but what if at some point in the future you alter the type to one with finer precision? Therefore, the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan

For more on this topic please see: "Beware of Between"

Please note the method provided by Scott is correct and very very useful.
I just don't recommend you try to use "the last point of the last day of any month" when there is a better alternative.
tanj1035Author Commented:
Thanks for your comment, Paul, it is very helpful!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.