Run a query based on today's date

Question

I am making a program that auto-runs using windows scheduler. What I'd like to do is set the program to run on the 1st and the 16th of every month. If the program run's on the 1st. I'd like to have the query run for last month... For example if today was the first of august I would want it to run 7/1/12 - 7/31/12. If I run the program on the 16th I want it to run the query for the current month to the 15th. For example if it were 8/16, I would want the program to run the query for 8/1/12 - 8/15/12.

Below is my query. It works great except it does not get the 1st of the month. (if it's the 15th or the 1st)

For example: if I run the query on 7/1/12 it should send the results for 6/1-6/30 Right now it's showing 6/2-6/30. If I run the query for 6/15 it should run 6/1 -6/15... it's running it for 6/2 - 6/15.

SELECT        
    Store_Number, Invoice_Number, Invoice_Date, Extended_Price, 
    Warranty_Amount, Quantity_Sold, Invoice_Detail_Code
FROM
    Invoice_Detail_Tb
WHERE        
    (Invoice_Date BETWEEN (CASE WHEN datepart(dd, getdate()) = 1 THEN dateadd(mm, - 1, getdate()) ELSE dateadd(dd, - 15, getdate()) END) 
                  AND (CASE WHEN datepart(dd, getdate()) = 1 THEN dateadd(dd, - 1, getdate()) ELSE dateadd(dd, - 1, getdate()) END)) 
    AND (Warranty_Amount > 0) 
    AND (Store_Number = '309')
ORDER BY 
    Store_Number, Invoice_Date

Any idea how to get the first of the month included in there?

`Invoice_Date` is a strict `DATE` type right, no time information included? Also, you could probably simplify things a good bit by making your upper-bound exclusive (ie, `<`), and just comparing it to 'today' (you also don't need that `CASE...` statement for the end date). — Clockwork-Muse, Aug 06 '12 at 20:12
There is a time stamp, but it's always 12:00AM as it's a database snapshot at time of extract (always 12:00AM). — Shmewnix, Aug 07 '12 at 19:01

Aaron Bertrand · Answer 1 · 2018-11-09T22:28:07.283

IMHO not much advantage to trying to calculate these start and end ranges within the query. Might be easier to visualize what is happening by separating it out:

DECLARE @sd SMALLDATETIME, @ed SMALLDATETIME;

-- set the start date to the first day of this month
SET @sd = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

IF DATEPART(DAY, GETDATE()) = 1 THEN
BEGIN
  -- if it's the first, we want last month instead
  SET @sd = DATEADD(M<ONTH, -1, @sd);
END

SET @ed = DATEADD(MONTH, 1, @sd);

SELECT Store_Number, ...etc...
FROM dbo.Invoice_Detail_Tb
WHERE Store_Number ='309' 
AND Invoice_Date >= @sd
AND Invoice_Date < @ed
ORDER BY Store_Number, Invoice_Date;

Stay far, far away from BETWEEN for date range queries; always use open-ended ranges:

score 0 · Answer 2 · edited Aug 07 '12 at 07:55

0

Perhaps this will get you started in the right direction:

-- Beginning of this month:
SELECT dateadd(mm, datediff(mm, 0, getdate()), 0)

-- Beginning of previous month:
SELECT dateadd(mm, datediff(mm, 0, getdate()) - 1, 0)

edited Aug 07 '12 at 07:55

Tisho

8,320
6
44
52

answered Aug 06 '12 at 20:14

cindy

21
1

Run a query based on today's date

2 Answers2