question

jenniferzen-7686 avatar image
0 Votes"
jenniferzen-7686 asked jenniferzen-7686 commented

Cast a varchar field with mixed time format to time (0)

Hi

I have a [time] field- varchar datatype and it has got values in mixed formats as below

14:24:00
07:00.0
10:00:56.7730000
54:80.0 (exclude wrong format)

I am trying to cast the field as time (0) but it is causing conversion error.
Can some one please help me with sort this out?

Thanks

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered jenniferzen-7686 commented

Try identifying the possible formats and interpretations. For example, for shown data, try something like this:

 select *, 
     try_cast(case when MyColumn like '[0-9][0-9]:[0-9][0-9].[0-9]%' then '00:' + MyColumn else MyColumn end as time(0)) as [Time]
 from MyTable

Show details if there are more cases.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Viorel-1

Worked like a charm :)

Thank u
Jenni

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered jenniferzen-7686 commented

Hi @jenniferzen-7686,

Welcome to Microsoft Q&A!

Please also refer below:

 declare @t table
 ([time] varchar(max))
    
 insert into @t values
  ('14:24:00'),
 ('07:00.0'),
 ('10:00:56.7730000'),
 ('54:80.0')
    
  select try_convert(time(0),IIF([time] like '[0-9][0-9]:[0-9][0-9].[0-9]' ,LEFT([time],5)+':00', [time])) as [Time]
  from @t

Output:

 Time
 14:24:00
 07:00:00
 10:00:57
 NULL

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Melissa,

Thanks for your help.

I don't adding 00 to the end will make as 07(hour):00(minute):00(sec). I assume its actually 00:07:00.

Jenni

0 Votes 0 ·