GETDATE in WHERE Clause

Question

GETDATE in WHERE Clause

rburke 87921

Say Hey Kid

Points: 671
More actions
March 9, 2010 at 2:05 pm

#221412

I have the following query;
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
and I want to return only the orders made today. If I use the GETDATE() function in a WHERE clause - nothing. I've tried the CONVERT funtion to remove the time element to no avail. Any help greatfully received.....

Viewing 15 posts - 1 through 15 (of 23 total)

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

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

rburke 87921 (3/9/2010)
I have the following query;
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
and I want to return only the orders made today. If I use the GETDATE() function in a WHERE clause - nothing. I've tried the CONVERT funtion to remove the time element to no avail. Any help greatfully received.....

DECLARE @Today DATE = GETDATE()

Query ...

WHERE dbo.[Order].OrderDate >= @Today

Converting oxygen into carbon dioxide, since 1955.

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

Here is your query rewritten:

SELECT

prd.ProductID,

prd.Category,

prd.ProductCode,

prd.Description,

prd.Price,

ord.CustomerID,

ord.OrderID,

ord.OrderDate

FROM

dbo.[Order] ord

INNER 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);

Please note how I truncate the time from the getdate() function. Also note how I use table aliases in the from clause to alias the column names in the ON and SELECT. Three and four part names are being depreciated and may not be supported in future versions of SQL Server, so you should get in the habit of using table aliases in the FROM clause.

Hope this helps.

martysharpster7 SSC Journeyman Points: 79 More actions · Answer 3

thanks for the information.

Las Vegas foreclosure homes - Las Vegas condos homes - Sunnyvale Realtor

rburke 87921 Say Hey Kid Points: 671 More actions · Answer 4

Hi Lynn - thanks for that but I still get no rows returned even though there is a record with the date 2010-03-10 in the order date field. Help!

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

rburke 87921 (3/10/2010)
Hi Lynn - thanks for that but I still get no rows returned even though there is a record with the date 2010-03-10 in the order date field. Help!

Please post the CREATE TABLE scripts for the order table.

^{“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

rburke 87921 Say Hey Kid Points: 671 More actions · Answer 6

USE [Development]

GO

/****** Object: Table [dbo].[Order] Script Date: 03/10/2010 09:38:23 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Order](

[OrderID] [int] IDENTITY(1,1) NOT NULL,

[CustomerID] [int] NOT NULL,

[ProductID] [int] NOT NULL,

[OrderDate] [date] NOT NULL,

[UserName] [nvarchar](15) NOT NULL,

CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED

(

[OrderID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Customer] FOREIGN KEY([CustomerID])

REFERENCES [dbo].[Customer] ([CustomerID])

GO

ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Customer]

GO

ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Product] FOREIGN KEY([ProductID])

REFERENCES [dbo].[Product] ([ProductID])

GO

ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Product]

GO

ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_User] FOREIGN KEY([UserName])

REFERENCES [dbo].[User] ([UserName])

GO

ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_User]

GO

ALTER TABLE [dbo].[Order] ADD CONSTRAINT [DF_Order_OrderDate] DEFAULT (getdate()) FOR [OrderDate]

GO

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

Thanks!

Try this first:

DECLARE @Today DATE = GETDATE()

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 = @Today

Edit: @steve-2 & Lynn - hope you don't mind me borrowing your code, lads. It helps to keep a level playing field for the OP.

^{“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

rburke 87921 Say Hey Kid Points: 671 More actions · Answer 8

I probably should have mentioned that I'm trying to create a view. Doesn't seem to like the DECLARE statement.

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

rburke 87921 (3/10/2010)
I probably should have mentioned that I'm trying to create a view. Doesn't seem to like the DECLARE statement.

Why try to turn a query into a view when you're unsure whether or not the query works? That's like trying to put the windows into a house before the walls are up 😀

Let's stick with a plain 'ol query until you've figured out what's going wrong, then wrap it in a view afterwards.

^{“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

rburke 87921 Say Hey Kid Points: 671 More actions · Answer 10

Forget that last post. I'm being a k*ob!. That works now thankyou very much.

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

rburke 87921 (3/10/2010)
Forget that last post. I'm being a k*ob!. That works now thankyou very much.

Heh funny! No you're not, you're learning.

Was it the inner join?

^{“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

rburke 87921 Say Hey Kid Points: 671 More actions · Answer 12

So how do I now incorporate;

DECLARE @Today DATE = GETDATE()

SELECT

prd.ProductID,

prd.Description,

prd.Price,

ord.OrderID,

ord.OrderDate

FROM dbo.[Order] ord

LEFT JOIN dbo.Product prd

ON ord.ProductID = prd.ProductID

WHERE ord.OrderDate = @Today

.......into a view?

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

This is the equivalent without using a variable:

SELECT

prd.ProductID,

prd.[Description],

prd.Price,

ord.OrderID,

ord.OrderDate

FROM dbo.[Order] ord

LEFT JOIN dbo.Product prd

ON ord.ProductID = prd.ProductID

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

rburke 87921 Say Hey Kid Points: 671 More actions · Answer 14

rburke 87921

Say Hey Kid

Points: 671

March 10, 2010 at 3:40 am

#1131241

Magic - Thanks very much Chris.