I have the following code and I am trying to do the following. If user_time is not a date then I want the data in user_date to be passed on into user_datetime. Is there a function that works similarly to isnull in sql server that can do this?

Thanks in advance

SELECT CASE WHEN isdate(user_time) = 0
THEN user_time = user_date
ELSE null
END AS user_datetime

Before:

Result:

CodePudding user response：

We can use CAST. We have to CAST user_date to DATETIME because we cannot add minutes to a type DATE.

create table test(
  user_time varchar(8),
  user_date date,
  user_date_time datetime);
  insert into test (user_time, user_date) values
  ('10:30:00','20220226'),
  ('13 hours','20220202');
  select * from test;
  
GO

user_time | user_date  | user_date_time
:-------- | :--------- | :-------------
10:30:00  | 2022-02-26 | null          
13 hours  | 2022-02-02 | null          

update test
set user_date_time =
CASE WHEN isdate(user_time) = 0
THEN  user_date
ELSE cast(user_date as datetime)   cast(user_time as datetime)
END ;
select * from test;

GO

user_time | user_date  | user_date_time         
:-------- | :--------- | :----------------------
10:30:00  | 2022-02-26 | 2022-02-26 10:30:00.000
13 hours  | 2022-02-02 | 2022-02-02 00:00:00.000

db<>fiddle here

CodePudding user response：

To check if a string contains a valid time it can be tested by trying to convert it with TRY_CONVERT or TRY_CAST.

And use that in a CASE WHEN

For example:

UPDATE your_table
SET user_datetime =
  CASE 
  WHEN TRY_CAST(user_time AS TIME) IS NOT NULL
  THEN CAST(CAST(user_date AT TIME ZONE 'UTC' AS DATE) AS DATETIME)
       CAST(TRY_CAST(user_time AS TIME) AS DATETIME) 
  ELSE user_date AT TIME ZONE 'UTC' 
  END
WHERE user_datetime IS NULL;

select *
from your_table;

user_date	user_time	user_datetime
2022-03-01 14:28:44.1630000 -04:00	16:00	2022-03-01 16:00:00.0000000 00:00
2022-03-01 14:28:44.1630000 01:00	Blah	2022-03-01 13:28:44.1630000 00:00

Test on db<>fiddle here