GETDATE in WHERE Clause

Question

Post reply

GETDATE in WHERE Clause

March 11, 2010 at 4:33 am

This was removed by the editor as SPAM

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

anna.ellis@getronics.com Default port Points: 1465 More actions · Answer 1

Or try this to get today's orders:

CREATE VIEW TodaysOrders

AS

SELECT

prd.ProductID,

prd.Category,

prd.ProductCode,

prd.Description,

prd.Price,

ord.CustomerID,

ord.OrderID,

ord.OrderDate

FROM dbo.[Order] ord

LEFT JOIN dbo.Product prd

ON ord.ProductID = prd.ProductID

WHERE ( ord.OrderDate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND

ord.OrderDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + 1, 0)

)

As Lynn has intimated, using GETDATE() as a comparison provides you with a specific value for the date, down to hours, minutes and seconds. Try executing SELECT GETDATE() a few times to see what we mean. Using the DATEADD/DATEDIFF functionality gives you the equivalent of the reporting TODAY() function.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Aeterna (3/10/2010)
Or try this to get today's orders:
CREATE VIEW TodaysOrders
AS
SELECT
prd.ProductID,
prd.Category,
prd.ProductCode,
prd.Description,
prd.Price,
ord.CustomerID,
ord.OrderID,
ord.OrderDate
FROM dbo.[Order] ord
LEFT JOIN dbo.Product prd
ON ord.ProductID = prd.ProductID
WHERE ( ord.OrderDate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND
ord.OrderDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + 1, 0)
)
As Lynn has intimated, using GETDATE() as a comparison provides you with a specific value for the date, down to hours, minutes and seconds. Try executing SELECT GETDATE() a few times to see what we mean. Using the DATEADD/DATEDIFF functionality gives you the equivalent of the reporting TODAY() function.

rburke 87921 (3/9/2010)
I want to return only the orders made today.

The OrderDate column is DATE type.

WHERE ord.OrderDate = CAST(GETDATE() AS DATE)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

anna.ellis@getronics.com Default port Points: 1465 More actions · Answer 3

Chris Morris-439714 (3/10/2010)
The OrderDate column is DATE type.
WHERE ord.OrderDate = CAST(GETDATE() AS DATE)

You're absolutely right. I chose to provide an alternative solution for those occasions where the OrderDate column might be a DATETIME datatype.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I'd be curious to know why this where clause didn't work:

WHERE ( ord.OrderDate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND

ord.OrderDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + 1, 0)

)

Just thinking about it logically, it should have worked.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Lynn Pettis (3/10/2010)
I'd be curious to know why this where clause didn't work:
WHERE ( ord.OrderDate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND
ord.OrderDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + 1, 0)
)
Just thinking about it logically, it should have worked.

Reckon it's 'cos there was no matching product?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Vipul Jaba SSC Enthusiast Points: 154 More actions · Answer 6

Try this:

SELECT

dbo.Product.ProductID,

dbo.Product.Category,

dbo.Product.ProductCode,

dbo.Product.Description,

dbo.Product.Price,

dbo.[Order].CustomerID,

dbo.[Order].OrderID,

dbo.[Order].OrderDate

FROM

dbo.[Order]

INNER JOIN dbo.Product ON dbo.[Order].ProductID = dbo.Product.ProductID

WHERE

convert(datetime,convert(varchar(20),dbo.[Order].OrderDate,106)) = convert(datetime,convert(varchar(20),getdate(),106))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Vipul Jaba (3/11/2010)
Try this:
SELECT
dbo.Product.ProductID,
dbo.Product.Category,
dbo.Product.ProductCode,
dbo.Product.Description,
dbo.Product.Price,
dbo.[Order].CustomerID,
dbo.[Order].OrderID,
dbo.[Order].OrderDate
FROM
dbo.[Order]
INNER JOIN dbo.Product ON dbo.[Order].ProductID = dbo.Product.ProductID
WHERE
convert(datetime,convert(varchar(20),dbo.[Order].OrderDate,106)) = convert(datetime,convert(varchar(20),getdate(),106))

dbo.[Order].OrderDate is no longer SARGable.

The INNER JOIN will lose orders with no product.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul White SSC Guru Points: 150467 More actions · Answer 8

stewartc-708166 (3/11/2010)
If the OrderDate column were of type DATETIME, try:
WHERE dbo.[Order].OrderDate BETWEEN CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AND CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) + 1

SARGable maybe...but definitely undocumented and unsupported.

Even more so in 2008 which has other date/time types.

The standard method to retain SARGability is:

SELECT DATEADD

(

DAY,

DATEDIFF

(

DAY,

CONVERT(DATETIME, '19000101', 112),

CURRENT_TIMESTAMP

),

CONVERT(DATETIME, '19000101', 112)

);

Any constant date can be used instead of '19000101', many programmers use the constant value zero.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi