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
 General SQL Server Forums
 New to SQL Server Programming
 WHERE Clause and DATETIME Field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cnbhold
Starting Member

USA
43 Posts

Posted - 07/14/2011 :  15:52:07  Show Profile  Reply with Quote
This following query returns this results set

SELECT RecordCreated
FROM Orders

7/14/2011 9:56:56 AM
7/14/2011 10:15:23 AM
7/13/2011 5:50:05 PM
7/13/2011 3:20:25 PM

How would I write the WHERE clause to pull all records for a specific date such as 7/13/2011. The RecordCreated is a DATETIME field.

Angel

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 07/14/2011 :  16:31:31  Show Profile  Reply with Quote
one way:
where recordcreated >= 7/13/2011 and recoredCreated < 7/14/2011

Be One with the Optimizer
TG
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 07/14/2011 :  18:37:54  Show Profile  Visit jcelko's Homepage  Reply with Quote
quote:
Originally posted by cnbhold

This following query returns this results set

SELECT RecordCreated
FROM Orders

7/14/2011 9:56:56 AM
7/14/2011 10:15:23 AM
7/13/2011 5:50:05 PM
7/13/2011 3:20:25 PM

How would I write the WHERE clause to pull all records for a specific date such as 7/13/2011. The RecordCreated is a DATETIME field.

Angel




Rows are not records; columns are not fields. The only temporal display format allowed in Standard SQL is ISO-8601. We never put audit meta data into the same table.

SELECT *
FROM Orders
WHERE CAST (order_datetime AS DATE) = @in_search_date;

This is very basic stuff. You might want to stop and do some reading or get some training.


--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/15/2011 :  05:11:01  Show Profile  Reply with Quote
quote:
Originally posted by jcelko
SELECT *
FROM Orders
WHERE CAST (order_datetime AS DATE) = @in_search_date;

This is very basic stuff. You might want to stop and do some reading or get some training.

Not only are you behaving like a total ass but you're giving bad advice as well! What would happen to this query of yours if the table had 30 mill rows (yes, ROWS!) and an index on this column??

cnbhold: stick with TG's suggestion, it is FAR superior to celkos.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 07/15/2011 :  07:06:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Lumbago, this is not entirely true for SQL Server 2008R2.
TG suggestion

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
       |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
            |--Index Seek(OBJECT:([tempdb].[dbo].[#Sample]), SEEK:([tempdb].[dbo].[#Sample].[dt] >= '2100-01-01 00:00:00.000' AND [tempdb].[dbo].[#Sample].[dt] < '2101-01-01 00:00:00.000') ORDERED FORWARD)


Celko suggestion

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1009],0)))
       |--Stream Aggregate(DEFINE:([Expr1009]=Count(*)))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1007], [Expr1008], [Expr1006]))
                 |--Compute Scalar(DEFINE:(([Expr1007],[Expr1008],[Expr1006])=GetRangeThroughConvert('2100-01-01','2100-01-01',(62))))
                 |    |--Constant Scan
                 |--Index Seek(OBJECT:([tempdb].[dbo].[#Sample]), SEEK:([tempdb].[dbo].[#Sample].[dt] > [Expr1007] AND [tempdb].[dbo].[#Sample].[dt] < [Expr1008]),  WHERE:(CONVERT(date,[tempdb].[dbo].[#Sample].[dt],0)='2100-01-01') ORDERED FORWARD)
SQL Server 2008 is smart enough to realize this is a range query and rewrites your query internally to get the seek anyway.

SET STATISTICS IO ON gives

-- TG
Table '#Sample'. Scan count 1, logical reads 3.

-- Celko
Table '#Sample'. Scan count 1, logical reads 3.



N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 07/15/2011 07:09:14
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 07/15/2011 :  07:10:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Oh... Forgot to post the sample data used in previous post.
CREATE TABLE	#Sample
		(
			dt DATETIME NOT NULL
		)

CREATE INDEX IX_Sample ON #Sample (dt)

INSERT		#Sample
		(
			dt
		)
SELECT		TOP(500000)
		DATEADD(MINUTE, CHECKSUM(NEWID()), '59000101')
FROM		master..spt_values AS v1
CROSS JOIN	master..spt_values AS v2



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/15/2011 :  07:28:59  Show Profile  Reply with Quote
That might be true Peso, but it's still a terrible advice. What sql server is doing in this case is to compensate for poor coding and even though it works it doesn't mean that this is how you should do it. This is probably also one of very few cases where the db engine is able to use an index after all but "everybody" (including you) knows that relying on implicit conversion, undocumented features, etc. is bound to give you trouble down the line somewhere and should be avoided.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/
Go to Top of Page

cnbhold
Starting Member

USA
43 Posts

Posted - 07/15/2011 :  09:12:16  Show Profile  Reply with Quote
Lumbago & Swepeso thanks for your help.
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/15/2011 :  09:36:35  Show Profile  Reply with Quote
BTW Peso; what is the query cost between the two? I'm just curious...

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 07/15/2011 :  09:52:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The query cost between batches is not really relevant. The execution plan is emailed to you.
CREATE TABLE	#Sample
		(
			dt DATETIME NOT NULL
		)

CREATE INDEX IX_Sample ON #Sample (dt)

INSERT		#Sample
		(
			dt
		)
SELECT		TOP(500000)
		DATEADD(MINUTE, CHECKSUM(NEWID()) / 1000, '20000101')
FROM		master..spt_values AS v1
CROSS JOIN	master..spt_values AS v2

-- Original (52% of batch)
SELECT	COUNT(*)
FROM	#Sample
WHERE	dt >= '20020804'
	AND dt < '20020805'

-- Celko (48% of batch)
SELECT	COUNT(*)
FROM	#Sample
WHERE	CAST(dt AS DATE) = '20020804'



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 07/15/2011 :  11:55:14  Show Profile  Visit jcelko's Homepage  Reply with Quote
The ISO/ANSI Standard CAST() and DATE data type are portable. Since they are Standard, they will be retained in future releases and optimized. It is also cleaner to read. In typography, there is a principle called the law of proximity; in English, things that are physically together on the page are seen as a unit; splitting up a single concept into multiple locations are not seen together. He also got the date formats wrong. See how foo_date is repeated? ugly.

foo_date >= '2011-07-13' AND foo_date < '2011-07-14'

Another way we faked this in the old days was:

foo_date BETWEEN '2011-07-13 00:00:00.000' AND '2011-07-13 23:59:59.999'

The advantage is that is shows the "between-ness" of the predicate; the dis-advantage is that the DATETIME limits are ugly.


--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/15/2011 :  12:33:56  Show Profile  Reply with Quote
What is the relevance of CAST() and DATE data type being standard/portable have to do with anything? The example peso posted is probably one in a million where using a function on a column in a where-clause is equally fast as working around it, and please don't tell me that you took this in to account when posting your solution.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/
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.09 seconds. Powered By: Snitz Forums 2000