CODEWITHSUNDEEP
sqldatetimeselect
DevDevDev
DevDevDev

Reputation: 5177

SQL statement help -- Select customers who ordered today

Say I have a table which stores customers order IDs. Such as

| Customer ID | Order ID | Order Date

How can I get all customers who have ordered today?

Also OrderDate would be a DateTime.

Something like

SELECT DISTINCT CustomerID
FROM TableName
Where OrderDate > Today

But the last part is what I can't figure out.

Upvotes: 5

Views: 2802

Answers (3)

shahkalpesh
shahkalpesh

Reputation: 33474

SELECT DISTINCT CustomerID
FROM TableName
Where OrderDate = CAST(GetDate() AS NUMERIC)

OR

SELECT DISTINCT CustomerID
FROM TableName
Where CAST(OrderDate AS NUMERIC) = CAST(GetDate() AS NUMERIC)

I have tried to see that it returns the same number for the given date with a different time. So, it should work when a date is converted to its numeric value.

SELECT CAST(0 AS DATETIME) returns 1/1/1900 12:00:00 AM

Upvotes: 0

Mark Brackett
Mark Brackett

Reputation: 85665

It's fairly common to only want a date out of a datetime - you should be able to Google for the specifics of your RDBMS (since you don't mention it). The important bit is to make your query SARGable by transforming today's date1 - not the order date.

For MSSQL, something like

SELECT DISTINCT CustomerID
FROM TableName
--I assume you want midnight orders as well - so use >=
Where OrderDate >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

would work by taking number of days today is from Date 0 (DATEDIFF(dd, 0, GETDATE())) and adding them back to Date 0 (DATEADD(dd, 0, x)). That's T-SQL specific, though.

1 If you were searching for an arbitrary date, you'd still transform both arguments:

SELECT DISTINCT CustomerID
FROM TableName
Where 
    OrderDate >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
    --You *do not* want midnight of the next day, as it would duplicate orders
    AND OrderDate < DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()) + 1)

Upvotes: 4

Brad Bruce
Brad Bruce

Reputation: 7827

In Oracle the statement would look something like:

SELECT DISTINCT CustomerID  
FROM TableName  
Where OrderDate >= trunc(sysdate)

SQL-Server should be similar

Upvotes: 0

Related Questions