Solved

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

Posted on 2014-03-13
8
413 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 143

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:cindyfiller
ID: 39927603
Unfortunately I'm still on 2008 eomonth won't work.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher 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:Scott Pletcher
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

829 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