Clever Geek Handbook

MS SQL query between (today) and 30 days ago

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

Request function: This request will be run through a batch file in Windows Task Scheduler. The goal is to create a CSV file every month.

My request:

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 column "DATEX" (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)     
    )

* INTERVAL ERROR is not recognized

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

* unrecognized syntax error 1

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

* unrecognized syntax next to "Where" 

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

* error about 'WHERE'

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

* non-boolean expression specified

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

* incorrect syntax next to ','

What is the correct Microsoft SQL query syntax to sort the "incident" table between "today" and "30 days ago"?

Please note that just using it Between 'YYYY-MM-DD' and 'YYYY-MM-DD'

doesn't help, because I will have to manually change the dates in order to run the query. I want to automate it to generate a CSV file every month.

+3


source to share


4 answers


Instead of throwing everything at the wall and seeing which sticks, I highly recommend reading the vendor's documentation . Microsoft SQL Server has extremely comprehensive and readable documentation, even among RDBMSs, both on the Internet and when installing the fantastic SQL Server Books Online .

As for your problem, you need to use DATEADD()

. However, the problem is that datetime fields and GETDATE()

have a time component and you need to take that into account.

So, one of the keys that you need to know is how to remove time from date and time. If you are using SQL Server 2008 R2 and above, you can do this:

CAST(GETDATE() AS DATE)

But that doesn't work on SQL Server 2008 and earlier. You need to use this instead :

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

If you know that DATEX will never be 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

What is: "DATEX is on or after midnight 30 days ago."

Now, if DATEX might be in the future but 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

What is: "DATEX is on or after midnight 30 days ago and earlier or now." On the other hand, if DATEX may 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 comes from or after midnight 30 days ago and until midnight tomorrow." It's easier to refer to "until midnight tomorrow" because datetime can have fractional seconds.

+1


source


You can use the Dateadd

Function also remove multiple conditions OR

and use the offerIN 



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
+1


source


Your request 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 demonstration

Hope it helps

0


source


This should work for your condition.

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

it will be minus 30 days from the current date

0


source






More articles:


All Articles