MS SQL query between (today) and 30 days ago

Question

Microsoft SQL 2008 (using Visual Studio 2010 to build and test query).

Query function: This query is going to be run via batch file in Windows Task Scheduler. The goal is to be able to create a CSV file every month.

My query:

SELECT     TYPE, DATEX, TIME, STREET, CROSS_ST, XCOORD, YCOORD
FROM       INCIDENT
WHERE      (TYPE = '644') OR
           (TYPE = '459') OR
           (TYPE = 'HS') OR
           (TYPE = '484') OR
           (TYPE = '487') OR
           (TYPE = '488') OR
           (TYPE = '10851') OR
           (TYPE = '187') OR
           (TYPE = '211') OR
           (TYPE = '245') OR
           (TYPE = '451')
ORDER BY DATEX

I am trying to sort the 'DATEX' column (which is in datetime format) between 'today's date' and '30 days ago'

I have tried all of these statements and none of them work:

(DATEX < DATEADD(month, - 1, GETDATE()))

DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), 
(
CONVERT(varchar, DATEX, 110) AS DATE,
    DATEX CONVERT(varchar, DATEADD(d,-30,GETDATE()), 23)     
    )

*ERROR INTERVAL is not recognized

AND DATEX BETWEEN DATEADD(mm,-1,GETDATE()) AND DATEADD(mm,1,GETDATE())

*error unrecognized syntax 1

=DateAdd("d", -7, Today())     *Today is not a recognized function
where date_col > DATEADD(day,-7,SYSDATETIME())

*unrecognized syntax near 'Where'

where DATEDIFF(day,date_col,SYSDATETIME()) < 7

*error near 'WHERE'

 DATEX Dte <  DATEADD(month, -2, GETDATE())

*an expression of non-Boolean type specified

AND (DATEX BETWEEN ({ fn CURDATE() }, 30) AND { fn CURDATE() })

*incorrect syntax near ','

What is the correct syntax for Microsoft SQL query to sort a table 'incident' between 'today's date' and '30 days ago'?

Please note that simply using Between 'YYYY-MM-DD' and 'YYYY-MM-DD' is not helpful because I would need to manually change the dates to run the query. I want to automate it to create a CSV file every month.

score 1 · Answer 1 · answered May 27 '15 at 02:21

You can use Dateadd Function also remove the multiple OR conditions and use IN clause

SELECT     TYPE, DATEX, TIME, STREET, CROSS_ST, XCOORD, YCOORD
FROM       INCIDENT
WHERE      DATEX BETWEEN DATEADD(dd,-30,GETDATE()) AND GETDATE() 
AND        TYPE in ('644','459','HS','484','487','488','10851','187','211','245','451')
ORDER BY DATEX

score 1 · Accepted Answer · edited May 23 '17 at 11:52

Instead of throwing everything at the wall and seeing what sticks, I strongly recommend reading the vendor's documentation. Microsoft SQL Server has extremely comprehensive and readable documentation, even among RDBMSs, both on the web and if you install the fantastic SQL Server Books Online.

As to your issue, you'll need to use DATEADD(). The problem, however, is that datetime fields and GETDATE() have a time component, and you need to account for that.

So, one of the keys you'll need to know, is how to strip the time from a datetime. If you're on SQL Server 2008 R2 and higher, you can do this:

CAST(GETDATE() AS DATE)

But that doesn't work on SQL Server 2008 and earlier. Instead, you need to use this:

DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

If you know that DATEX is never in the future, you can use this:

SELECT TYPE, DATEX, TIME, STREET, CROSS_ST, XCOORD, YCOORD
FROM INCIDENT
WHERE TYPE IN ('644','459','HS','484','487','488','10851','187','211','245','451')
    AND DATEX >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 30, 0) 
ORDER BY DATEX

Which is, "DATEX is on or after midnight 30 days ago."

Now, if DATEX might be in the future, but it always has a zero time value, you can use this:

SELECT TYPE, DATEX, TIME, STREET, CROSS_ST, XCOORD, YCOORD
FROM INCIDENT
WHERE TYPE IN ('644','459','HS','484','487','488','10851','187','211','245','451')
    AND DATEX BETWEEN DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 30, 0) AND GETDATE()
ORDER BY DATEX

Which is, "DATEX is on or after midnight 30 days ago and on or before right now." On the other hand, if DATEX might be in the future and will have a non-zero time component, you should use this:

SELECT TYPE, DATEX, TIME, STREET, CROSS_ST, XCOORD, YCOORD
FROM INCIDENT
WHERE TYPE IN ('644','459','HS','484','487','488','10851','187','211','245','451')
    AND DATEX >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 30, 0) 
    AND DATEX <  DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + 1, 0)
ORDER BY DATEX

This is "DATEX is on or after midnight 30 days ago and before midnight tomorrow." It's easier to refer to "before midnight tomorrow" because datetime can have fractional seconds.

CrApHeR · Answer 3 · 2015-05-27T02:48:55.947

Your query should be something like this

SELECT     TYPE, DATEX, TIME, STREET, CROSS_ST, XCOORD, YCOORD
FROM       INCIDENT
WHERE      DATEX > DATEADD(dd, -30, CAST(CAST(GETDATE() AS INT) AS DATETIME)) AND
           ((TYPE = '644') OR
            (TYPE = '459') OR
            (TYPE = 'HS') OR
            (TYPE = '484') OR
            (TYPE = '487') OR
            (TYPE = '488') OR
            (TYPE = '10851') OR
            (TYPE = '187') OR
            (TYPE = '211') OR
            (TYPE = '245') OR
            (TYPE = '451'))
ORDER BY DATEX

Here you can find a working demo

Hope this helps

score 0 · Answer 4 · answered May 27 '15 at 02:14

0

This should work for your condition.

WHERE DATEX BETWEEN GETDATE()-30 AND GETDATE()

it will minus 30 days off from todays date

answered May 27 '15 at 02:14

HJK

100
9

MS SQL query between (today) and 30 days ago

4 Answers4