views:

378

answers:

3

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.

A: 

In Oracle the statement would look something like:

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

SQL-Server should be similar

Brad Bruce
SQL Server doesn't have a `trunc` statement.
Eric
Wow. I'll have to remember that the next time I interview a candidate.Every few months I interview someone and hear something like "SQL is SQL". It doesn't seem to matter that they've been using SQL Server for years and we are an Oracle shop.
Brad Bruce
+1  A: 

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)
Mark Brackett
A: 
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

shahkalpesh