Solved

SQL going back to start and end of month for 2 months ago

Posted on 2014-03-13
8
401 Views
Last Modified: 2014-03-13
I need to build out 2 parameters for a start and end date that goes back 2 months.  In this example, it is currently March so my start date will be 1/1/2014 and my end date will be 1/31/14.

I got the start date with this:   =DateAdd("m", -2, DateSerial(Year(Now()), Month(Now()), 1))

If I use this for the end date:  =DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1))
 it obviously brings in 2/28/14 instead of 1/31/14.  I'm not sure how to combine the 2 for my end date so it becomes 1/31/14.
0
Comment
Question by:cindyfiller
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39927560
Use another dateadd (month,-1, .... ) to achieve that result
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39927573
fyi SQL Server 2012 introduces the EOMONTH function, which will return the end of the month for any passed day.

SELECT EOMONTH(GETDATE()) when run now returns 2014-03-31.
0
 

Author Comment

by:cindyfiller
ID: 39927601
I was trying to do another dateadd, but wasn't doing it in the right spot - it was bombing out on me.  Something wasn't right!
0
 

Author Comment

by:cindyfiller
ID: 39927603
Unfortunately I'm still on 2008 eomonth won't work.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39927667
Maybe endDate =

 DateAdd("d", -1, DateAdd("m", 1, DateSerial(Year(Now()), Month(Now()), 1)))

?
0
 

Author Comment

by:cindyfiller
ID: 39927682
Thank you - that worked with one slight tweak.   I had to do -1 for DateAdd("m", -1...
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39927772
D'OH, sorry, quite right, I misread the end date you were looking as being the end of the current month, not the previous month.
0
 

Author Comment

by:cindyfiller
ID: 39927792
It was easy to fix and your code worked, so I sincerely appreciate it!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now