Error when using ISDATE in CASE WHEN

Asked

Viewed 160 times

2

Hello, everybody. I am running the query below and is showing the error: "Operand type Clash: int is incompatible with date".

My intention in performing this query is to validate whether the field VENCIMENTO_ORIGINAL is a valid date, and if so, validate whether the day of this date is equal to the day of the DTINICIO field. Does anyone know what could be going wrong? Follow the query below:

  UPDATE A
SET DIVERGENCIA_DE_VENCIMENTO = 'NÃO'
FROM  TMP_LAYOUT_DIRETRIZES A
WHERE ISDATE(A.VENCIMENTO_ORIGINAL) = 1
AND CASE WHEN ISDATE(A.VENCIMENTO_ORIGINAL) = 0 THEN CAST(NULL AS DATE)
    ELSE DAY(CONVERT(DATE,VENCIMENTO_ORIGINAL,103))
    END = DAY(CONVERT(DATE,DTINICIO,103))

EDIT: The following is an example of the data in the TMP_LAYOUT_DIRETRIZES table:

vencimento_original dtinicio
2018-09-20          20/09/2014
2018-09-20          20/09/2015
2018-09-20          20/12/2015
2018-09-20          20/06/2016
2018-09-20          20/08/2016
2018-09-20          20/03/2017
2018-09-20          20/09/2017
2018-09-28          20/04/2018
2018-09-20          20/07/2018
2018-09-20          20/07/2018
sql sql-server

  • CAST(NULL AS DATE)? That doesn’t make any sense. Put it straight NULL in that case, without the CAST

  • 1

    When I do this, there appears another error: Conversion failed when Converting date and/or time from Character string.

  • Paul, in this case, neither CAST nor CONVERT will work. NULL is different from 'NULL', and in this case any conversion p/ date on sql server you will need to have a date that is possible to convert.

  • Put an example of the data you have in this table TMP_LAYOUT_DIRETRIZES to see how your field is VENCIMENTO_ORIGINAL and DTINICIO

  • Your dates don’t follow a pattern. There are some that are YYYY-MM-DD and others are DD/MM/YYYY

  • I put SET DATEFORMAT DMY at the beginning of the query, and still could not convert. Both fields are varchar.

Show 1 more comment

1 answer

0


Your date format is not equal for both fields:

UPDATE A
   SET DIVERGENCIA_DE_VENCIMENTO = 'NÃO'
  FROM  TMP_LAYOUT_DIRETRIZES A
 WHERE ISDATE(A.VENCIMENTO_ORIGINAL) = 1
   AND CASE
         WHEN ISDATE(A.VENCIMENTO_ORIGINAL) = 0 THEN NULL
         ELSE DAY(CONVERT(DATE, VENCIMENTO_ORIGINAL, 120))
       END = DAY(CONVERT(DATE, DTINICIO, 103))

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.