March 21, 2008 at 6:25 am
i'm attempting to use datediff to determine elapsed days between 2 fields defined as "int" with the values being in yyyymmdd format. this is how we define dates in our database.
select datediff(day, adate, bdate) as elapseddays
from atable, btable
from looking at the date fields, i know adate is greater than bdate.
the error message is:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
So, I have to cast the script like followings:
select
DATEDIFF(DAY, CAST(CAST(aDate AS CHAR(8)) AS DATETIME), CAST(CAST(bDate AS CHAR(8)) AS DATETIME)) as elapseddays
from atable, btable
However, I wonder in the BOL topic "CAST and CONVERT") says: "Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST (CONVERT) function to be specified. This chart shows all explicit and implicit data type conversions allowed for SQL Server system-supplied data types, including bigint and sql_variant."
And also from the diagram in the BOL, the conversion from INT to DATETIME is implicit.
As specified in BOL , from type INT converted to type DATETIME, we do not need to cast or convert because such conversion is implicit. I do not know why we need to use CAST function over here?
Thank you.
March 21, 2008 at 9:45 am
Internally sql server stores dates as integers so the date you are using is probably outside the range that sql server recognizes as a date, so by casting to character and then date allows sql server to recognize it as a date format and not an integer.
This is one reason why you should use SQL Servers date data types when working with dates.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 21, 2008 at 9:54 am
johnsql (3/21/2008)
As specified in BOL , from type INT converted to type DATETIME, we do not need to cast or convert because such conversion is implicit. I do not know why we need to use CAST function over here?
Because your integers are not encoded in the correct way for integer-coded dates, which as I recall is something like "integer = (days since Dec 31, 1899)". This would make today around 37000~ when coded as an integer.
Since your format is "int = Year*10000 + MM*100 + DD" today would be 20080321. When this is is decoded into a date using the correct form (above) it gives a date somewhere around the year 56,900, which is well past DateTime's upper-limit of 9,999. Thus the overflow.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply