Time - Need to convert varchar to time

Question

Post reply

Time - Need to convert varchar to time

tan110

Hall of Fame

Points: 3855
More actions
January 27, 2010 at 9:48 am

#386187

Need to update/convert a varchar column to time.
Example
varcharTimeColumn
-------------------
00.13.00
Trying to update another column I created.
timeColumn
------------
00.13.00
update table
set timeColumn = varcharTimeColumn
keep getting the error:
Conversion failed when converting date and/or time from character string.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

Try CASTING the value to time to be sure it is in the proper format.

tan110 Hall of Fame Points: 3855 More actions · Answer 2

I tried this:

update dbo.VerizonDetails

set totalTime2 = cast(totalTime as time)

but got the errror:

Msg 241, Level 16, State 1, Line 2

Conversion failed when converting date and/or time from character string.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

tan110 (1/27/2010)
I tried this:
update dbo.VerizonDetails
set totalTime2 = cast(totalTime as time)
but got the errror:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Try:

update dbo.VerizonDetails

set totalTime2 = cast(REPLACE(totalTime, '.', ':') as time)

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

tan110 Hall of Fame Points: 3855 More actions · Answer 4

I went ahead and replaced the '.' with ':', and reran the update statement:

update dbo.VerizonDetails

set totalTime2 = totalTime

still getting error:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

tan110 Hall of Fame Points: 3855 More actions · Answer 5

Also tried this:

update dbo.VerizonDetails

set totalTime2 = cast(totalTime as time)

but getting error message:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Curious, the following test code worked just fine on my system:

declare @ TimeVal varchar(16); -- Added space between @ and TimeVal to get code to post

set @TimeVal = '00.13.00';

select @TimeVal, cast(replace(@TimeVal,'.',':') as time)

tan110 Hall of Fame Points: 3855 More actions · Answer 7

its weird, when I run:

declare @ TimeVal varchar(16); -- Added space between @ and TimeVal to get code to post

set @TimeVal = '00.13.00';

select @TimeVal, cast(replace(@TimeVal,'.',':') as time)

declare @ TimeVal varchar(16); -- Added space between @ and TimeVal to get code to post

set @TimeVal = '00:13:00.0';

select @TimeVal, cast(@TimeVal as time)

Both scripts above work fine.

But when running in an update statement:

update dbo.VerizonDetails

set totalTime2 = cast(totalTime as time)

still getting error:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

tan110 (1/27/2010)
its weird, when I run:
declare @ TimeVal varchar(16); -- Added space between @ and TimeVal to get code to post
set @TimeVal = '00.13.00';
select @TimeVal, cast(replace(@TimeVal,'.',':') as time)
declare @ TimeVal varchar(16); -- Added space between @ and TimeVal to get code to post
set @TimeVal = '00:13:00.0';
select @TimeVal, cast(@TimeVal as time)
Both scripts above work fine.
But when running in an update statement:
update dbo.VerizonDetails
set totalTime2 = cast(totalTime as time)
still getting error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Please show results of this:

select top 5 totalTime from dbo.VerizonDetails;

tan110 Hall of Fame Points: 3855 More actions · Answer 9

tan110

Hall of Fame

Points: 3855

January 27, 2010 at 10:56 am

#1109441

00:13:00

00:01:00

00:03:00

00:09:00

00:01:00

tan110 Hall of Fame Points: 3855 More actions · Answer 10

Thanks everyone, was able to get it working with set based statement. Had to loop through all the records.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

tan110 (1/27/2010)
Thanks everyone, was able to get it working with set based statement. Had to loop through all the records.

Care to share?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

tan110 (1/27/2010)
Had to loop through all the records.

Heh... GAHHH!!!! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 13

tan110 (1/27/2010)
Thanks everyone, was able to get it working with set based statement. Had to loop through all the records.

Would you care to elaborate on how a set-based method is looping through all the records? Seems like an oxy-moron to me...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 14

tan110 (1/27/2010)
its weird, when I run:
declare @ TimeVal varchar(16); -- Added space between @ and TimeVal to get code to post
set @TimeVal = '00.13.00';
select @TimeVal, cast(replace(@TimeVal,'.',':') as time)
declare @ TimeVal varchar(16); -- Added space between @ and TimeVal to get code to post
set @TimeVal = '00:13:00.0';
select @TimeVal, cast(@TimeVal as time)
Both scripts above work fine.

Sure, first script uses "." which becomes a ":" due to replacement. Second script directly uses ":", so there is no replacement required.

BTW

Please share your solution (and you don't need a loop 😉 )

Greets

Flo