Converting a date

Question

Converting a date

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 1

Luis Cazares (12/4/2015)
PhilPacha (12/4/2015)
A CAST on the database column would likely preclude the use of an available index, while using the native values would allow index usage, if available. The >= and < pair is the best choice.
You're right. However, casting a datetime column to date, still allows an index to be used. I'm not sure why would it happen like that, but it does. You could test it if you want.

Yeah, it was surprising when I discovered that there are times when functions in a query predicate don't affect whether or not an index is used. Don't expect such good fortune with:

substring(x, i, j) like '%blah%

🙂

Don Simpson

I'm not sure about Heisenberg.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 2

It's probably worth mentioning that the CAST/CONVERT in the predicate only works when using certain datatypes.

Casting a DATETIME (and apparently FLOAT) to DATE will allow a seek but casting a VARCHAR to a date will cause a scan...

Using Luis's test script...

--===== Conditionally drop the test table to make reruns easier.

IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL

DROP TABLE #SomeTestTable

;

--===== Declare some obviously named variables

DECLARE @NumberOfRows INT,

@StartDate DATETIME,

@EndDate DATETIME,

@Days INT --This is the "range"

;

--===== Preset the variables to known values

SELECT @NumberOfRows = 1000000,

@StartDate = '20150101', --Inclusive

@EndDate = '20200101', --Exclusive

@Days = DATEDIFF(dd,@StartDate,@EndDate)

;

--===== Create the test table with "random constrained" integers and floats

-- within the parameters identified in the variables above.

SELECT TOP (@NumberOfRows)

SomeRandomDateTime = RAND(CHECKSUM(NEWID())) * @Days + @StartDate

INTO #SomeTestTable

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

--===== Change the SomeRandomDateTime into a VARCHAR before indexing

ALTER TABLE #SomeTestTable ALTER COLUMN SomeRandomDateTime VARCHAR(30) NOT NULL

;

--===== Create an index on the datetime column

CREATE INDEX IXDateDatetime ON #SomeTestTable(SomeRandomDateTime);

;

--===== Show ten rows of the table

SELECT *

FROM #SomeTestTable

WHERE CAST(SomeRandomDateTime as date) = CAST(GETDATE() AS date);

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Jason A. Long (12/6/2015)
It's probably worth mentioning that the CAST/CONVERT in the predicate only works when using certain datatypes.

And only on certain versions of CQL Server.

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) - performing Index Scan.

_____________
Code for TallyGenerator

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Sergiy (12/6/2015)
Jason A. Long (12/6/2015)
It's probably worth mentioning that the CAST/CONVERT in the predicate only works when using certain datatypes.
And only on certain versions of CQL Server.
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) - performing Index Scan.

The code above is not a good example demonstrating that casting a datetime to a date is sargable. I tested that code in 2008r2, 2012, 2014 and all of them produced the exact same execution plan with an index scan.

It has however been documented and proven many times that the date to datetime conversions (and the inverse) are in fact sargable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Sean Lange (12/7/2015)
Sergiy (12/6/2015)
Jason A. Long (12/6/2015)
It's probably worth mentioning that the CAST/CONVERT in the predicate only works when using certain datatypes.
And only on certain versions of CQL Server.
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) - performing Index Scan.
The code above is not a good example demonstrating that casting a datetime to a date is sargable. I tested that code in 2008r2, 2012, 2014 and all of them produced the exact same execution plan with an index scan.
It has however been documented and proven many times that the date to datetime conversions (and the inverse) are in fact sargable.

Just to be sure. What code were you testing?

My code produces an index seek on Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) and Microsoft SQL Server 2012 - 11.0.2100.60 (X64).

Jason's code will generate index scans as the column is a varchar(30).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Luis Cazares (12/7/2015)
Sean Lange (12/7/2015)
Sergiy (12/6/2015)
Jason A. Long (12/6/2015)
It's probably worth mentioning that the CAST/CONVERT in the predicate only works when using certain datatypes.
And only on certain versions of CQL Server.
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) - performing Index Scan.
The code above is not a good example demonstrating that casting a datetime to a date is sargable. I tested that code in 2008r2, 2012, 2014 and all of them produced the exact same execution plan with an index scan.
It has however been documented and proven many times that the date to datetime conversions (and the inverse) are in fact sargable.
Just to be sure. What code were you testing?
My code produces an index seek on Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) and Microsoft SQL Server 2012 - 11.0.2100.60 (X64).
Jason's code will generate index scans as the column is a varchar(30).

I was responding to Sergiy's comment about the code from Jason producing a scan. I knew the reason was because of the varchar. And yes your example will produce a seek as described. Gosh my ability to communicate effectively on the forums has been awful lately. :hehe: Definitely needing this vacation that starts later this week.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/