I have the following code in SQL Server:

[siteWindowStart] = case
        when loc.LOCATION_TYPE_ID = '1'
        and ps.STOP_PLANNED_START_DATE is null
            then concat(pt.[TOUR_PLANNED_START_DATE], 'T', '09:00:00')
        when loc.LOCATION_TYPE_ID = '1'
        and ps.STOP_PLANNED_START_DATE is not null
            then concat(ps.STOP_PLANNED_START_DATE, 'T', '09:00:00')
        when loc.LOCATION_TYPE_ID in  ('6', '7', '8')
        and ps.STOP_REQUIRED_DELIVERY_DATE is not null
            then concat(dateadd(day, -1, ps.STOP_REQUIRED_DELIVERY_DATE), 'T' ,ps.[STOP_REQUIRED_DELIVERY_TIME])
        when loc.LOCATION_TYPE_ID in ('6', '7', '8')
        and ps.STOP_REQUIRED_DELIVERY_DATE is null
            then CONCAT(dateadd(day, -1, pt.[TOUR_PLANNED_START_DATE]), 'T', pt.[TOUR_PLANNED_START_TIME])
        else null
        end

For this part of the code...

and  ps.STOP_REQUIRED_DELIVERY_DATE is not null
            then concat(ps.STOP_REQUIRED_DELIVERY_DATE, 'T', dateadd(minute, 30, ps.[STOP_REQUIRED_DELIVERY_TIME]))

I need to ensure that it only returns the concat if both ps.stop_required_delivery_date AND ps.stop_required_delivery_time are both not null.

I tried adding:

and ps.stop_required_delivery_time is not null 

but this simply returned the else statement which is 'NULL' !

How do I code this to only return the concat text if both columns are not null? And if one is null whilst the other is populated return

then concat(pt.[TOUR_PLANNED_START_DATE], 'T', dateadd(minute, 30, pt.[TOUR_PLANNED_START_TIME]))

Many thanks in advance

CodePudding user response：

IS NULL and IS NOT NULL work perfectly in a CASE as in the following example.
I've used datatypes date and time to make it as close to your case as possible without having your table definitions and sample data.

CREATE TABLE t ( a date, b time);
insert into t values ('2022-01-01','10:00'),('2022-01-01',null),(null,'10:00'),(null,null);

GO

4 rows affected

select  
        a,
        b,
        case
        when a is not null and b is not null then concat(a,'T',b)
        when a is not null and b is null then 'a-null'
        when a is null and b is not null then 'null-b'
        when a is null and b is null then 'null-null'
        else 'not possible' 
        end "a-b"
  from t
GO

a          | b                | a-b                        
:--------- | :--------------- | :--------------------------
2022-01-01 | 10:00:00.0000000 | 2022-01-01T10:00:00.0000000
2022-01-01 | null             | a-null                     
null       | 10:00:00.0000000 | null-b                     
null       | null             | null-null                  

db<>fiddle here