CODEWITHSUNDEEP
sql-servert-sqldatetimesql-server-2012
Zerotoinfinity
Zerotoinfinity

Reputation: 6540

Datetime is NULL when time format differs

I have specific time scenario which can be 4 digit, 6 digit or can be NULL or string as mentioned below. Here in scenario 3 & 4 my method of calculating datetime is not working and coming as NULL Is there any way to get date with 00:00:00:000 as time for case 3 & 4? & for 1 it should be 10:02:00:000

DECLARE @DATE VARCHAR(10) =CAST(GETDATE() AS DATE)
DECLARE @Time1 VARCHAR(10) = '1002'
DECLARE @Time2 VARCHAR(10) = '160634'
DECLARE @Time3 VARCHAR(10) = '0900XX'
DECLARE @Time4 VARCHAR(10) = ''


SELECT TRY_CONVERT(DATETIME, @DATE +' '
                                  +LEFT(ltrim(@Time1), 2) 
                                  + ':' + SUBSTRING(@Time1, 3, 2)
                                            + ':' + RIGHT(rtrim(@Time1), 2)) , TRY_CONVERT(TIME, @Time1), @Time1 AS Time

SELECT TRY_CONVERT(DATETIME, @DATE +' '
                                  +LEFT(ltrim(@Time2), 2) 
                                  + ':' + SUBSTRING(@Time2, 3, 2)
                                            + ':' + RIGHT(rtrim(@Time2), 2)) , TRY_CONVERT(TIME, @Time2), @Time2 AS Time

SELECT TRY_CONVERT(DATETIME, @DATE +' '
                                  +LEFT(ltrim(@Time3), 2) 
                                  + ':' + SUBSTRING(@Time3, 3, 2)
                                            + ':' + RIGHT(rtrim(@Time3), 2)) , TRY_CONVERT(TIME, @Time3), @Time3 AS Time

SELECT TRY_CONVERT(DATETIME, @DATE +' '
                                  +LEFT(ltrim(@Time4), 2) 
                                  + ':' + SUBSTRING(@Time4, 3, 2)
                                            + ':' + RIGHT(rtrim(@Time4), 2)) , TRY_CONVERT(TIME, @Time4), @Time4 AS Time

enter image description here

Upvotes: 0

Views: 96

Answers (2)

Thom A
Thom A

Reputation: 95569

I would, personally, "pad out" the values to be 6 digits, inject the : characters, and then use TRY_CONVERT. Then you use ISNULL to return midmight for failed converions:

SELECT ISNULL(TRY_CONVERT(time(0),STUFF(STUFF(RIGHT('000000' + V.VarcharTime,6),5,0,':'),3,0,':')),'00:00:00')
FROM (VALUES(@Time1),
            (@Time2),
            (@Time3),
            (@Time4))V(VarcharTime);

If 1002 is meant to be 10:02:00 rather than 00:10:02 then pad on the right, rather than the left:

SELECT ISNULL(TRY_CONVERT(time(0),STUFF(STUFF(LEFT(V.VarcharTime+'000000',6),5,0,':'),3,0,':')),'00:00:00')
FROM (VALUES(@Time1),
            (@Time2),
            (@Time3),
            (@Time4))V(VarcharTime);

Upvotes: 1

lptr
lptr

Reputation: 6788

..................
DECLARE @Time4 VARCHAR(10) = ''

select @Time1 = concat(cast(@Time1 as varchar(8)), replicate('0', 8));
select @Time2 = concat(cast(@Time2 as varchar(8)), replicate('0', 8));
select @Time3 = concat(cast(@Time3 as varchar(8)), replicate('0', 8));
select @Time4 = concat(cast(@Time4 as varchar(8)), replicate('0', 8));

SELECT TRY_CONVERT(DATETIME, @DATE +' '......................

Upvotes: 0

Related Questions