Error using ISDATE in CASE WHEN

Question

Error using ISDATE in CASE WHEN

Navigation

#1 by (0 votes)

2

Hello everyone. I'm running the query below and it is displaying the error: "Operand type clash: int is incompatible with date".

My intent when performing this query is to validate if the ORIGINAL_ORIGINAL field is a valid date, and if so, validate if the day of this date is the same as the day of the DTINITION field. Does anyone know what could be going wrong? Here is 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: Here 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

asked by anonymous 22.10.2018 / 21:40

1 answer

Composite key in laravel migrate Share facebook button, how to customize?

score 0 · Accepted Answer

Your date format is not the same 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))