Solved

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

Posted on 2014-03-13
8
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Expert Comment

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

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
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)

 

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

695 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