0

I would like to store dates in the format CCYYMMDD in Teradata, but I fail to do so. Find below what I tried so far:

query 1:

SEL CAST(CAST(CURRENT_DATE AS DATE FORMAT 'YYYYMMDD') AS VARCHAR(8))
-- Output: 20191230  ==> this works!

query 2:

SEL CAST(CAST(CURRENT_DATE AS DATE FORMAT 'CCYYMMDD') AS VARCHAR(8))
-- output: SELECT Failed.  [3530] Invalid FORMAT string 'CCYYMMDD'.

It seems that the CCYYMMDD is not available in Teradata right away. Is there a workaround?

Tool used: Teradata SQL assistant

normanius
  • 8,629
  • 7
  • 53
  • 83
Sivakumar Anandhan
  • 1
  • 1
  • It looks like `CC` refers to the first two digits of the year (i.e. 20 in 2019 or 19 in 1998). https://stackoverflow.com/questions/33420395/what-does-ccyymmdd-date-format-mean?rq=1. So `CCYY` should be equivalent to `YYYY` – ravioli Dec 30 '19 at 12:31
  • Thanks for the response @ravioli. The business requirement is to store the century number in place of 'CC'. e.g 2019-12-30 should be stored as '21191230'. – Sivakumar Anandhan Dec 30 '19 at 17:29
  • OK, I understand now. See my answer below. – ravioli Dec 30 '19 at 18:08

1 Answers1

0

Internally, dates are stored as integers in Teradata. So when you say you want to store them in a different format, I don't think you can do that. But you can choose how to display / return the values.

I'm sure there's a cleaner way to get the format you want, but here's one way:

WITH cte (mydate) AS (
  SELECT CAST(CAST(CURRENT_DATE AS DATE FORMAT 'YYYYMMDD') AS CHAR(8)) AS mydate
)
SELECT 
  CAST(
    (CAST(SUBSTRING(mydate FROM 1 FOR 2) AS INTEGER) + 1) -- generate "century" value
    AS CHAR(2) -- cast value as string
  ) || SUBSTRING(mydate FROM 3) AS new_date -- add remaining portion of date string
FROM cte

SQL Fiddle - Postgres

You'd have to add some extra logic to handle years before 1000 and after 9999. I don't have a TD system to test, but give it a try and let me know.

ravioli
  • 3,749
  • 3
  • 14
  • 28