Conversion from string to date not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conversion from string to date not working

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-12-10 : 12:15:26

select ID
convert(datetime,year+'-'+month+'-'+day,112) as birthdy1
from bio_table

I am converting three separate text fields
year char(4)
month char(2)
day char(2)
from a string to a concatenated date. Why am I getting the error
Conversion failed when converting datetime from character string. it should be a fairly easy conversion.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-10 : 12:43:20

For me...

select convert(datetime,'2009'+'-'+'03'+'-'+'12',112) as birthdy1 --doesn't work
select convert(datetime,'2009'+'03'+'12',112) as birthdy1 --works

So you could try removing the hyphens. Unless there's some dodgy data...

select convert(datetime,'20x9'+'03'+'12',112) as birthdy1 --doesn't work

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-10 : 12:45:54

For 112 you do not use '/' or '-', If you DO use 112 be weary that if you have a single digit day or Month your concat will not work right.

Example: Day = 1 Month = 5 Year = 2009
Concat: 200915

declare @Year varchar(4)
declare @Month varchar(2)
declare @day varchar(2)
Declare @Date Varchar(10)

Set @Year = '2009'
Set @Month = '10'
Set @Day = '30'
Set @Date = @Year + @Month + @Day

Select @Date
Select convert(datetime,@date,112) as birthdy1

If you want Slashes -

select convert(datetime,'2009'+'-'+'03'+'-'+'12',111) as birthdy1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources