IsDate in TSQL Issue

Question

Hi there

I have a bug on our app and it points that if there is a date format like this:

SELECT IsDate('4:27:01') -- ok
SELECT IsDate('25:01') ---> not ok
SELECT IsDate('23:01') ---> ok

In our data, sometimes the format only 5 characters which means it's only for minutes and seconds.

What is best way to handle this?

Answer 1

Easiest way might be to add "00:" when the length is 5 characters.
e.g. :

declare @str varchar(100)
set @str = '25:01'
-- if length is 5, interpret as  00:mi:ss
set @str = (CASE WHEN LEN(@str)=5 THEN '00:' + @str ELSE @str END)
select ISDATE(@str) -- shows 1
select cast(@str as time)  -- shows 00:25:01.000