July 29, 2014 at 12:21 pm
I've recently accepted a new DBA job. In fact I'm there first DBA. While trying to build a few queries I came across on that was made by someone in the company with limited SQL exposure. To solve the problem of calculating the number of days from a date he came up with a cleaver solution:
cast(t.[CONTR_DATE] - '2014/06/30' as real(1)) as Days
Now I would use DATEDIFF but as he is not a SQL person that is what he found on Google. So my questions are, why and how does that work? Is this happenstance or is there valid logic to this? Thanks in advance!!
Fred Stemp
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
July 29, 2014 at 12:43 pm
(Note: This answer may need some correcting by others with more knowledge!)
Basically, by casting the date math as REAL, the subtraction being done is calculated with each date being the number of days since 1900/01/01, the "zero date" for SQL Server. For example, try running this code:
SELECT CAST(GETDATE() AS real(1))
You'll notice that you get an answer of 41847.61, as of the time of this writing. Divide by 365, and you get 114.65. 2014 - 114.64 = 1900/01/01, after you account for date precision, leap years, and possibly some other internal stuff that I'm not aware of :-).
If you had a date of 2014/07/01 in your query above, you could use the CAST function to get their actual values to get a better picture of how it's working:
SELECT CAST(CAST('2014/07/01' AS datetime) AS real(1))
SELECT CAST(CAST('2014/06/30' AS datetime) AS real(1))
(There's probably a more elegant way of doing this than a double-CAST, I'd imagine; going off to research that myself!)
You'll get values of 41819 and 41818 for the two lines, respectively; subtract, and you get a value of 1, which is indeed the difference in the number of days between the two.
It's a little clunky way of doing the date math, and properly-done DATEDIFFs would more than likely be more readily comprehensible and efficient, but it's workable. The CAST may cause some unnecessary overhead that could be avoided by working with the dates directly, but I'm not certain on how extensive the performance difference would be offhand. I'll do a little testing myself for curiosity's sake 🙂
EDIT: Did some performance testing on a table with a datetime column in my environment with 1.2 million rows. Ran it a few times each on the entire column, then on a range of 30 days. My results:
CAST to REAL-type math:
Full column: Scan count 1, logical reads 5345, CPU time = 297 ms, elapsed time = 304 ms.
30 days: Scan count 1, logical reads 128, CPU time = 0 ms, elapsed time = 8 ms
DATEDIFF(DAY,datecolumn,@Date):
Full column: Scan count 1, logical reads 5345, CPU time = 328 ms, elapsed time = 322 ms
30 days: Scan count 1, logical reads 128, 0 ms, elapsed time = 6 ms
Huh. There actually wasn't as much overhead on the REAL method as I thought there would be, and it was actually faster running on the entire column by a tiny bit. Interesting! I'm not certain what to make of the difference (other than variability in my own environment), but someone else may be more knowledgeable 🙂
- 😀
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply