Do you have a SQL Datataype conversion chart, ie a chart that represents if for eg an INT datatype can be converted to Datetime... etc...?
I dont need the one on SQL Books online. But need some other chart that lists out the datatype covnersions in SQL
If you are using T-SQL to do the 'conversion', the chart in Books Online is the definitive source.
And since it is the most complete source, I guess I don't understand what you are hoping to find.
|||What does this mean? "lists out the datatype covnersions in SQL"
There are a couple of things here. Are you talking about implicit conversions? Or what types you can convert using cast and convert? Both are included in the chart in BOL in the CAST and CONVERT section.
Can you give us a sample of what you are looking for?
|||Btw, if you don't find the information in the Books Online topic useful or not satisfactory then please use the "Send Feedback" link in the topic. This will help you to provide feedback directly to the topic owners and will help improve the quality of the documentation.|||I tried to convert an INT to a datetime, it gave an error "Arithmetic overflow error converting expression to data type datetime."
So I first converted it to varchar, and then to datetime, then it worked fine. eg
select convert(datetime,convert(varchar,xxx)),xxx,* from tblq
However, when I looked up, the BOL, it shows That Conversion from INT TO DATETIME, AND VARCHAR TO DATETIME Are both IMPLICT... Then how come the convert from Int to datetime errored out....?
I guess I am not able to Interpret the BOL Doc, for Implied and Explict conversions.. If it says impied conversion for both Varchar to datetime, and Int to datetime-- then why does 1 work and not the other.
|||Sounds like a typo in the conversion -number too big, or perhaps, not a number ...
The following works as expected:
select convert( datetime, 2958463 )
9999-12-31 00:00:00.000
The following fails with the error you received -only 1 digit larger...
select convert( datetime, 2958464 )
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
what's the integer you're trying to convert as datetime? if it's greater than 2,958,463, it will produce that error because i think sqlserver supports datetime from 01/01/17? [convert(datetime, -53690)] to 12/31/9999 [convert(datetime, 2958463)].
- clintz|||
The Integer I am trying to convert is a large one, ie
select convert(datetime,20060404)
It is has a date value.
But I still need to know what is the diff between the Implicit and explicit conversions. BOL was not clear enough.
|||Is 20060404 an integer, or a date? (It looks like a date to me...)
There is a really, really, big difference.
(The interger value 38809 would convert to 2006/04/04.)
As I, and others indicated in other responses to this post, the largest integer value that will convert to a datetime is 2958463. Your number, 20060404, greatly exceeds that value and WILL always cause an overflow error.
|||If your data looks like 20060404 and is stored as interger values, and you wish to convert them to datetime values, try something like this:
First convert (or cast) to char(8), and then cast that to a datetime.
Code Snippet
SELECT cast( cast( 20060404 AS char(8)) AS datetime )
2006-04-04 00:00:00.000
