today and yesterday orders - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

today and yesterday orders

Author

Topic

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-14 : 07:27:23

Hi,
I want to retrieve orders related to today and yesterday(all hour of yesterday means time between 00:00:00 and 23:59:59).
My order_date and today_date columns data type are DATETIME.

Here are my sample table and data:

CREATE TABLE [Sample]
(
order_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
order_date DATETIME,
today_date DATETIME
)

INSERT INTO Sample
(
order_date,
today_date
)
VALUES
(
'2012-12-31T12:00:00','2013-01-01T23:59:59'
)

CREATE NONCLUSTERED INDEX IX ON Sample (order_date)

In the above sample data I need to select the row but by following query no row was returned.

SELECT *
FROM Sample
WHERE order_date BETWEEN DATEADD(day, -1, today_date) AND today_date

So I decided to create another query like this:

SELECT *
FROM Sample
WHERE order_date BETWEEN DATEADD(day, -1, CONVERT(date, today_date)) AND today_date

Now I interested to know a simplified and standard way for achieving this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-15 : 03:56:50

see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

You've to make sure today_date field is of date related datatype or else convert it to date. For that values in the field should be of consistent and unambiguos format.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-15 : 14:06:44

[code]DECLARE @Sample TABLE
(
Order_ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
Order_Date DATETIME NOT NULL,
Today_Date DATETIME NOT NULL
);

INSERT @Sample
(
Order_Date,
Today_Date
)
VALUES ('2012-12-31T12:00:00', '2013-01-01T23:59:59');

-- SwePeso
SELECT Order_ID,
Order_Date,
Today_Date
FROM @Sample
WHERE Order_Date >= DATEDIFF(DAY, '19000102', Today_Date)
AND Order_Date < DATEDIFF(DAY, '18991231', Today_Date);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-15 : 14:52:46

Thank you both visakh16 and SwePeso.
SwePeso, unfortunately the code does not sense very for me, could you please explain the logic behinde of this criteria?

Also is below query same with yours?


--SwePeso
SELECT	Order_ID,
	Order_Date,
	Today_Date
FROM	@Sample
WHERE	Order_Date >= DATEDIFF(DAY, 1, Today_Date)
	AND Order_Date < DATEDIFF(DAY, -1, Today_Date);

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-16 : 13:09:01

Sigmas,

DATEDIFF compares two dates. You are relying on an implicit conversion from 1 & -1 to the dates that Peso has in his code. Peso, is also relying on an implict conversion from a number (result of datediff) to a date. In order to remove the implicit conversion from Peso's code, the DATEDIFF could be used with a DATEADD to return a date value.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-16 : 13:57:24

Or...

-- SwePeso
SELECT	Order_ID,
	Order_Date,
	Today_Date
FROM	@Sample
WHERE	Order_Date >= DATEADD(DAY, DATEDIFF(DAY, '19000102', Today_Date), '19000101')
	AND Order_Date < DATEADD(DAY, DATEDIFF(DAY, '18991231', Today_Date), '19000101');

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources