'SQL Server combine case with convert statement

Hi im stuck with this query, what i want is to get one column "fecha_registro_convertida" with the condition of the case statement but also with the CONVERT statement so i can get the date converted properly with the case statement

SELECT top 50 rs_IDCliente idCliente, CONVERT(datetime, 
           SWITCHOFFSET(CONVERT(datetimeoffset, 
                                c.rs_fecharegistro), 
                        DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
   AS fecha_registro_convertida,
CASE WHEN rs_fecharegistro IS NULL THEN c.CreatedOn ELSE rs_fecharegistro END 
          rs_fecharegistro, 
          isnull(BirthDate, '19900101') fechaNacimiento,
          suc.rs_IDSucursalregistro AS SucursalRegistro
FROM NMP_MSCRM.dbo.contact c
INNER JOIN NMP_MSCRM.dbo.rs_sucursalregistro suc
ON c.rs_SucursalRegistro=suc.rs_sucursalregistroId

Image of the records that i get

sqlsql-server


Solution 1:[1]

The SQL as posted delivers 5 columns: fecha_registro_convertida, fecha_registro_convertida, rs_fecharegistro, fechaNacimiento, SucursalRegistro.

Based on the comments, you want to have just 4 columns in the result: fecha_registro_convertida, fecha_registro_convertida, fechaNacimiento, SucursalRegistro.

Again based on comments, I believe you want fecha_registro_convertida to be the result of a SWITCHOFFSET on the column rs_fecharegistro or (if the value of rs_fecharegistro is null) on the column c.rs_fecharegistro.

This is quite easy to solve, by using ISNULL within the CONVERT function. I.e. replace

CONVERT(datetimeoffset, c.rs_fecharegistro)

with

CONVERT(datetimeoffset, ISNULL(c.rs_fecharegistro, c.CreatedOn))

Based on those assumptions, the SQL you need could be:

SELECT top 50 
     rs_IDCliente AS idCliente
    ,CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, ISNULL(c.rs_fecharegistro, c.CreatedOn), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS fecha_registro_convertida
    ,ISNULL(BirthDate, '19900101') AS fechaNacimiento
    ,suc.rs_IDSucursalregistro AS SucursalRegistro

FROM       NMP_MSCRM.dbo.contact c
INNER JOIN NMP_MSCRM.dbo.rs_sucursalregistro suc
        ON c.rs_SucursalRegistro=suc.rs_sucursalregistroId

(Note that is has been pointed out by @Larnu in the comments that the resulting rows are indeterminate because you have a TOP 50 restriction without an ORDER BY clause - but I am not trying to solve that issue).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Brett

Related Questions