Data Type Conversion

owain · October 31st, 2003, 09:54 AM

I have been reading Robert Vieira's book 'Professional SQL Server 2000 Programming' and on page 45 he discuses data type conversions and presents a chart showing the allowable conversions and whether they are explicit or implicit conversions.

The chart states that the datetime and smalldatetime data types will implicitly convert to char, varchar, nchar and nvarchar data types, however the example that follows the chart demonstrates that the string "Today's date is " can not be concatenated with the return value of the GETDATE() function unless it is explicitly converted to a varchar data type.

Quote:

quote:[u]From pages 45-46 of Pro SQL Server 2000 Programming</u>

SELECT 'Today''s date is ' + GETDATE()

The problem is that this statement would yield the following result

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Not exactly what we were after, is it? Now let's try it with the CONVERT() function:

SELECT "Today's date is " + CONVERT(varchar(12), GETDATE(),101)

This yields something like:

--------------------------
Today's date is 01/01/2000

(1 row(s) affected)

As far as I can see this appears to be in contradiction to the data type conversion chart. I have run the example and an error is generated if I do not explicitly convert the data type as would be expected.

Am I reading the chart wrong or is the chart wrong?

Regards
Owain Williams

Jonax · October 31st, 2003, 10:14 AM

As I haven't read the book in question, I can't comment on the chart therein. However: If you take a look at the CAST and CONVERT page in Books Online you'll see that it will convert implicitly in both directions. I don't see any mentioning of whether the order of the datatypes is prioritized, but if it is, I read the chart as saying that it will attempt to cast implicitly from char to date before it attempts the opposite direction...

owain · October 31st, 2003, 11:45 AM

Thanks for you help. It looks like you are correct. When I try:

Code:

SELECT '2000-01-01' + GETDATE()

It works without any errors. Suddenly it all begins to make sense.

Regards
Owain Williams

pgtips · October 31st, 2003, 11:48 AM

I don't know for certain, but what Jonax says seems to agree with the error message in your book extract. Note that the message says "Syntax error converting datetime from character string.", it doesn't say "error converting the date to a char". So that would seem to imply that its attempting to convert the string 'Today''s date is ' to a datetime.

hth
Phil

owain · October 31st, 2003, 11:53 AM

When I try:

Code:

SELECT GETDATE() + ' is today''s date'

I get the same error. This I presume means that SQL Server will never automatically convert a date/time data type into a string data type. Does anyone have an idea on why this is the case. It is not a problem, I am just curious.

Regards
Owain Williams

Jonax · October 31st, 2003, 12:31 PM

If my previous ramblings about the priority of conversions are correct it's clear why it (still) will attempt the 'wrong' direction.
If you however ask it to do an unambigious implicit conversion as in the following, it works...

Code:

DECLARE @TestVar VARCHAR(200)
SET @TestVar = GETDATE()
SELECT @TestVar