Data update

Question

Data update

Viewing 4 posts - 16 through 18 (of 18 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

John Mitchell-245523 (12/13/2016)
spectra (12/13/2016)
I don't want to change datetime to any other datatype e.g date .......
Why not? You're not changing any data if you cast as date. I understand it's still sargable, as well, although I haven't actually tested. Or just do it as Chris suggested.
SELECT j FROM #John
WHERE CAST(j AS date) BETWEEN '20161201' and '20161213'
John

I have tested and it is SARGable. It's all about the internal storage of the datetime data type.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Ed Wagner (12/13/2016)
John Mitchell-245523 (12/13/2016)
spectra (12/13/2016)
I don't want to change datetime to any other datatype e.g date .......
Why not? You're not changing any data if you cast as date. I understand it's still sargable, as well, although I haven't actually tested. Or just do it as Chris suggested.
SELECT j FROM #John
WHERE CAST(j AS date) BETWEEN '20161201' and '20161213'
John
I have tested and it is SARGable. It's all about the internal storage of the datetime data type.

Confirmed, been using it for years.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101118 More actions · Answer 3

You still don't need to cast the column, so why get into that bad habit? Yes, SQL corrects it in this case (at least for now?!). Why not just stick to a "best practice" method of the comparison anyway?:

WHERE date >= '20161201' AND date < '20170101'

Btw, do you know for sure if SQL would round up Dec 31 at 11:59:59.997 to Jan 1 the next year or not? You very well might, but I didn't, I had to test it. Therefore, I still prefer the above method.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 4

ChrisM@Work (12/13/2016)
spectra (12/13/2016)
I would like to upgrade this query a bit ...
CREATE TABLE #John (j datetime)
INSERT INTO #John VALUES ('2016-11-10 12:12:42.100')
SELECT j FROM #John
WHERE j BETWEEN '20161201' and '20161213'
If I have data on 13th I want to return that well.....what changes I need to add to this query. I don't want to change datetime to any other datatype e.g date .......Is there any way ?
Make the upper bound "less than the 14th"

I'm using this now. Hope this is all right.

thanks