CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Le_RedditUser
Le_RedditUser

Reputation: 113

No future date SQL Query

Below is a query that tags the order_date either in the 'less than 14 days' or 'less than 30 days' section. There's one order date that's a future date and it's also included in 'less than 14 days'.

I tried the following:

WHEN DATEDIFF(DD,OH.order_date, GETDATE()) < 14 AND DATEDIFF(DD,OH.order_date, GETDATE()) < GETDATE() THEN 'Less than 14 days'

Future date is still there. Please advise

Original code:

SELECT CS.cust_id, OH.order_date,
    CASE
    WHEN DATEDIFF(DD,OH.order_date, GETDATE()) < 14 THEN 'Less than 14 days'
    WHEN DATEDIFF(DD,OH.order_date, GETDATE()) < 30 THEN 'Less than 30 days'
    END
 AS 'orderAge'
FROM customers AS CS
INNER JOIN order_headers AS OH ON OH.cust_id = CS.cust_id
ORDER BY cust_id, order_date;

Upvotes: 0

Views: 183

Answers (2)

user3393753
user3393753

Reputation: 86

    SELECT CS.cust_id, OH.order_date,
        CASE
        WHEN DATEDIFF(DD,OH.order_date, GETDATE()) < 0 THEN 'Future date'
        WHEN DATEDIFF(DD,OH.order_date, GETDATE()) < 14 THEN 'Less than 14 days'
        WHEN DATEDIFF(DD,OH.order_date, GETDATE()) < 30 THEN 'Less than 30 days'
        END
     AS 'orderAge'
    FROM customers AS CS
    INNER JOIN order_headers AS OH ON OH.cust_id = CS.cust_id
    ORDER BY cust_id, order_date;

Upvotes: 3

Fred Fan Zhang
Fred Fan Zhang

Reputation: 21

Try your code but use 0 as a boundary point. You want positive return values of the DATEDIFF function only.

WHEN DATEDIFF(DD,OH.order_date, GETDATE()) < 14 AND DATEDIFF(DD,OH.order_date, GETDATE()) > 0 THEN 'Less than 14 days'

Upvotes: 1

Related Questions