CODEWITHSUNDEEP
sql-serverdatetime
user1947491
user1947491

Reputation: 11

Datetime conversion in SQL Server

declare @v1 datetime = getdate();
declare @v2 int = 2;
select @v1 + @v2;
---------------------------
2013-01-06 08:16:20.620

But 

declare @v1 datetime = getdate();
declare @v2 char(1) = '2';
select @v1 + @v2;
--------------------------

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.

gives error. But both int @v2 and char @v2 have the same value?

Upvotes: 1

Views: 259

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

When combining two operators of different types you will have an implicit type conversion. What gets converted is controlled by the Data Type Precedence rules.

Both char and int has lower precedence than datetime so your query is the equivalent of:

select @v1 + cast(@v2 as datetime);

Casting int value 2 to a datetime works just fine and gives you 1900-01-03 00:00:00.000

Casting a char value 2 to a datetime does however not work since the string 2 can not be interpreted as a valid datetime value.

Upvotes: 3

bystander
bystander

Reputation: 549

when char was converted to datetime.. it is usually false..because some chars can't be converted to datetime..and can overflow..just like '2','a' etc..

you need a char value which like '2013-01-04'...they can be converted correctly..

Upvotes: 0

Related Questions