Author |
Topic |
cnbhold
Starting Member
43 Posts |
Posted - 2011-07-14 : 15:52:07
|
This following query returns this results setSELECT RecordCreatedFROM Orders7/14/2011 9:56:56 AM7/14/2011 10:15:23 AM7/13/2011 5:50:05 PM7/13/2011 3:20:25 PMHow 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/2011Be One with the OptimizerTG |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-07-14 : 18:37:54
|
quote: Originally posted by cnbhold This following query returns this results setSELECT RecordCreatedFROM Orders7/14/2011 9:56:56 AM7/14/2011 10:15:23 AM7/13/2011 5:50:05 PM7/13/2011 3:20:25 PMHow 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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-07-15 : 05:11:01
|
quote: Originally posted by jcelkoSELECT * 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.- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
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-- TGTable '#Sample'. Scan count 1, logical reads 3.-- CelkoTable '#Sample'. Scan count 1, logical reads 3. N 56°04'39.26"E 12°55'05.63" |
|
|
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 v1CROSS JOIN master..spt_values AS v2 N 56°04'39.26"E 12°55'05.63" |
|
|
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.- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
cnbhold
Starting Member
43 Posts |
Posted - 2011-07-15 : 09:12:16
|
Lumbago & Swepeso thanks for your help. |
|
|
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...- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
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 v1CROSS JOIN master..spt_values AS v2-- Original (52% of batch)SELECT COUNT(*)FROM #SampleWHERE dt >= '20020804' AND dt < '20020805'-- Celko (48% of batch)SELECT COUNT(*)FROM #SampleWHERE CAST(dt AS DATE) = '20020804' N 56°04'39.26"E 12°55'05.63" |
|
|
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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
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.- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|