0

This is in Teradata.

I am trying to write a case statement that can work if the data comes in the following format:

YYYY-MM-DD ex. 2021-12-17
MM/DD/YYYY ex. 12/17/2021
YYYYMMDD   ex. 20211217
Excel Serial ex. 44547 Note: This is an Excel format that counts days from 1899-12-30, so 44547 is 12/17/2021

The case statement below works if the data comes in the formats of YYYYMMDD or Excel Serial, but if the data comes in the formats of YYYY-MM-DD or MM/DD/YYYY it only works if I take out the last 2 when's. Otherwise I receive an error for "Datatype Mismatch in THEN/ELSE expression"

CASE
WHEN POSITION('-' IN SUBSTR(DW_EFF_DT, 5)) <> 0 THEN CAST(DW_EFF_DT AS DATE FORMAT 'YYYY-MM-DD')
WHEN POSITION('/' IN SUBSTR(DW_EFF_DT, 5)) <> 0 THEN CAST(DW_EFF_DT AS DATE FORMAT 'MM/DD/YYYY')
WHEN CHARACTERS(TRIM(DW_EFF_DT))=8 THEN CAST(SUBSTR(DW_EFF_DT,1,4)||'-'||SUBSTR(DW_EFF_DT,5,2)||'-'||SUBSTR(DW_EFF_DT,7,2)) AS DATE FORMAT 'YYYY-MM-DD')
WHEN CHARACTERS(TRIM(DW_EFF_DT))=5 THEN CAST('1899-12-30' AS DATE FORMAT 'YYYY-MM-DD') + DW_EFF_DT
ELSE NULL
END AS DW_EFF_DT

Thank you for any help, it is much appreciated.

Edit: Thanks for the answers. They helped me figure it out. The fix is the last When statement needed to look like this:

WHEN CHARACTERS(TRIM(DW_EFF_DT))=5 THEN (CAST(CAST('1899-12-30' AS DATE FORMAT 'YYYY-MM-DD') + DW_EFF_DT AS DATE FORMAT 'YYYY-MM-DD'))
acC
  • 1
  • 1
  • A *case expression* returns a single value (a column) and this can only be a single data type; I don't know teradata but it's clear from the error that you are mixing data types - ensure you `cast` if necessary so every outcome returns the same type. – Stu Dec 17 '21 at 19:21
  • try casting your `else null` as date as well I've had a problem with that in other languages/RDBMS. I'm not sure it would be a problem here; but worth a try. Also; are you sure the casts will work in ALL cases? if an attempt of a cast were to throw an error... I guess the SQL itself would fail – xQbert Dec 17 '21 at 19:37
  • Thanks. I tried casting else null as date but it still returned the same error. – acC Dec 17 '21 at 19:41
  • Are you sure the dates are MM/DD/YYYY and not DD/MM/YYYY such that you have some 13+ in the first two places where it's unable to cast? or some "Bad Data" in some of the date/month positions. maybe [trycast](https://docs.teradata.com/r/1DcoER_KpnGTfgPinRAFUw/F6zt2mwBYPon4B7qTL0A5Q) instead of cast? – xQbert Dec 17 '21 at 19:46
  • Also: I'm assuming this is the only case statement in the select... I'd hate to be debugging the wrong one... Lastly: take a subset of your data (perhaps 1 of each value) and run it through. Does it work? If so it proves out your select and implies bad data somehwere. – xQbert Dec 17 '21 at 19:52
  • Thanks for trying. Still seeing what I can figure out. It is the only Case statement. – acC Dec 17 '21 at 20:17
  • I think I narrowed down the issue: The WHEN STATEMENT: "WHEN CHARACTERS(TRIM(DW_EFF_DT))=5 THEN CAST('1899-12-30' AS DATE FORMAT 'YYYY-MM-DD') + DW_EFF_DT" is not being interpreted as a date datatype. – acC Dec 17 '21 at 20:50
  • DW_EFF_DT is not an integer, it's a string. `THEN date'1899-12-30' + CAST(DW_EFF_DT AS INTEGER)` – Fred Dec 17 '21 at 21:09
  • I'm really unclear on what exactly you are trying to do. But it looks like maybe you are trying to parse randomly formed strings as dates? Make them all strings in what you think is a YYYY-MM-DD format. Then use trycast on those, see if you have any that aren't actually dates. Your last when bit makes no sense. – Andrew Dec 17 '21 at 22:08
  • If you come to an answer for your own question, post an answer, not an edit to the question – Caius Jard Dec 17 '21 at 23:50

0 Answers0