SQL Start date is current day then end date is 1 month ago even if its not from the first to the last of the month

Question

Ok I am trying to write a query that says get the current date and make it the start date. Then I want to go a month back from that current date for the EndDate. Is it possible to do this? Like if it was 9-15-2010 as the start date can I go a month back from that to 8-15-2010 or is this no possible....what would you do for like 9-20-2010 as the start date since every month has a different amount of days in it? Otherwise if this is not possible how else could I do this? The report will always be run on the 25th of the month so any ideas? I need to go from the 25th back a month....I can get some duplicate records between months if needed but less is obviously better

Right now I am using this:

DECLARE @StartDate DATETIME,
 @EndDate DATETIME;

SET @StartDate = DATEADD(m,-1,GETDATE());
SET @EndDate = DATEADD(m, 1, @StartDate);

Does this work?

Also, how would I then say my AuditInsertTimestamp is between @Start adn @EndDate?

Currently I have this:

AND cvn.[AuditInsertTimestamp] BETWEEN @StartDate AND @EndDate ;

This is still giving me dates like 7-26-2010 though....

Thanks!

Answer 1

That should work. Did you try it?

If it doesn't work (and there are only 12 test cases to check if you don't trust the documentation) then you can re-build the date from the date parts.

Here's the problem. It should be like this:

cvn.[Subject] = 'Field Changed (Plate Type)'
    AND (
        cvn.[Note] LIKE 'Old Type: IRP%New Type: BASE PLATE%'
        OR cvn.[Note] LIKE 'Old Type: Base Plate%New Type: IRP%'
    )
    AND cvn.AuditInsertTimestamp BETWEEN GETDATE() AND DATEADD(MONTH, -1, GETDATE()) 

AND takes precidence over OR, so you were picking up anything with Old Type:IRP or in the correct date range (with Old Type: Base Plate)

Answer 2

Yes it should work as expected.

Answer 3

Based on your comment:

Well this is being used to select records. So if I run it on the 25th I need 30 days back then my field AuditInsertTimestamp needs to be between these 2 dates.

I think you need to do something like this:

SELECT * FROM Table
WHERE AuditInsertTimestamp BETWEEN GETDATE() AND DATEADD(MONTH, -1, GETDATE())