SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 today and yesterday orders
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/14/2013 :  07:27:23  Show Profile  Reply with Quote
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.



Edited by - sigmas on 09/14/2013 07:30:26

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/15/2013 :  03:56:50  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 09/15/2013 :  14:06:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/15/2013 :  14:52:46  Show Profile  Reply with Quote
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);
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/16/2013 :  13:09:01  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 09/16/2013 :  13:57:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000