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
 General SQL Server Forums
 New to SQL Server Programming
 WHERE Clause and DATETIME Field

Author  Topic 

cnbhold
Starting Member

43 Posts

Posted - 2011-07-14 : 15:52:07
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-14 : 16:31:31
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

547 Posts

Posted - 2011-07-14 : 18:37:54
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

3271 Posts

Posted - 2011-07-15 : 05:11:01
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

30421 Posts

Posted - 2011-07-15 : 07:06:44
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-15 : 07:10:10
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

3271 Posts

Posted - 2011-07-15 : 07:28:59
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

43 Posts

Posted - 2011-07-15 : 09:12:16
Lumbago & Swepeso thanks for your help.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-15 : 09:36:35
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

30421 Posts

Posted - 2011-07-15 : 09:52:33
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

547 Posts

Posted - 2011-07-15 : 11:55:14
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

3271 Posts

Posted - 2011-07-15 : 12:33:56
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
   

- Advertisement -