| Author |
Topic |
|
sarahc
Starting Member
2 Posts |
 Posted - 2008-03-14 : 17:37:52
|
| Good afternoon!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 elapseddaysfrom atable, btablefrom looking at the date fields, i know adate is greater than bdate. the error message is:Server: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type datetime.do i need to reformat the date? and how? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-14 : 17:47:42
|
Yes. This wayDATEDIFF(DAY, CAST(CAST(aDate AS CHAR(8)) AS DATETIME), CAST(CAST(bDate AS CHAR(8)) AS DATETIME))
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
sarahc
Starting Member
2 Posts |
Posted - 2008-03-17 : 12:06:50
|
| Thank you so very much....i'll try this! |
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2008-03-19 : 15:10:48
|
quote:
Originally posted by Peso
Yes. This wayDATEDIFF(DAY, CAST(CAST(aDate AS CHAR(8)) AS DATETIME), CAST(CAST(bDate AS CHAR(8)) AS DATETIME))
E 12°55'05.25"N 56°04'39.16"
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?Thanks. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-19 : 15:55:32
|
quote:
Originally posted by johnsql
quote:
Originally posted by Peso
Yes. This wayDATEDIFF(DAY, CAST(CAST(aDate AS CHAR(8)) AS DATETIME), CAST(CAST(bDate AS CHAR(8)) AS DATETIME))
E 12°55'05.25"N 56°04'39.16"
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?Thanks.
I think you will realize why you might want to use CAST if you test doing the implicit conversion to see what happens.CODO ERGO SUM |
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2008-03-19 : 16:00:37
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by johnsql
quote:
Originally posted by Peso
Yes. This wayDATEDIFF(DAY, CAST(CAST(aDate AS CHAR(8)) AS DATETIME), CAST(CAST(bDate AS CHAR(8)) AS DATETIME))
E 12°55'05.25"N 56°04'39.16"
Yes, I got the error but I do not understand why 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.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?Thanks.
I think you will realize why you might want to use CAST if you test doing the implicit conversion to see what happens.CODO ERGO SUM
|
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2008-03-19 : 16:02:34
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by johnsql
quote:
Originally posted by Peso
Yes. This wayDATEDIFF(DAY, CAST(CAST(aDate AS CHAR(8)) AS DATETIME), CAST(CAST(bDate AS CHAR(8)) AS DATETIME))
E 12°55'05.25"N 56°04'39.16"
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?Thanks.
I think you will realize why you might want to use CAST if you test doing the implicit conversion to see what happens.CODO ERGO SUM
Yes, I got the error but I do not understand why 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.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? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-19 : 17:19:43
|
The conversion from INT to DATETIME works, but probably not the way you expect or want. See what happens when you run this:select convert(datetime,0), convert(datetime,39524) CODO ERGO SUM |
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2008-03-20 : 08:17:16
|
quote:
Originally posted by Michael Valentine Jones
The conversion from INT to DATETIME works, but probably not the way you expect or want. See what happens when you run this:select convert(datetime,0), convert(datetime,39524) CODO ERGO SUM
Michael Valentine Jones, How do you understand the phrase in the BOL (topic 'cast and convert')? I am confused about that BOL phrase:quote:
Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function
If the BOL says so, then if I do not use CAST or CONVERT function in the OP's question, I got the error when running the script in QA. I am confused the word without in the BOL phrase.Thanks for your explanation if possible,johnsql |
|
|
|
|
datediff