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

jennifer zen 341 Reputation points
2021-03-24T11:10:38.2+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,691 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments
Accepted answer
  1. Viorel 112.1K Reputation points
    2021-03-27T20:51:34.437+00:00

    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 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-03-29T09:30:49.927+00:00

    Hi @jennifer zen ,

    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.