Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

Hi All,

I have three timestamp(6) columns and i have to find the greatest value out of these columns. So i am using GREATEST function and below is the query for the same:

CAST(GREATEST(CAST(COALESCE(CAST(LST_QLFYNG_USE_DTTM AS DATE), DATE '1970-01-01') AS VARCHAR(10)),
CAST(COALESCE(CAST(LST_REFL_DTTM AS DATE), DATE '1970-01-01') AS VARCHAR(10)),
CAST(COALESCE(CAST(LST_RECHRG_DTTM AS DATE), DATE '1970-01-01') AS VARCHAR(10))
) AS DATE) AS FNL_LST_DATE

 

This query is running fine but issue is coming while converting from date to varchar(10).

What exaclty happening is this, suppose I have a date 2017-09-05 in one of the three columns, then after converting it to VARCHAR output string is coming like 09/05/17 in SQL ASSISTANT.

Now after calculating greatest value when i am casting it back to date the result coming is 1917-09-05 which is wrong. Expected result is 2017-09-05. 

Intersting part begins here!!!

when i am running the same SQL through BTEQ and taking the output in a log file, there its giving correct output. 

Can someone help me if its SQL ASSISTANT issue or some format issue.

 

Thanks,

Sauhard


Accepted Solutions
Junior Contributor

Re: Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

Hi Dave,

those stupid LEAST/GREATEST functions don't support date/time, at least it's fixed in TD16.10 :-)

 

Instead of VarChar it's better to cast to INT, more efficient and format independent.

1 ACCEPTED SOLUTION
7 REPLIES
Senior Apprentice

Re: Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

Hi Sauhard,

 

I think a couple of things are happening.

 

When your code CASTs the date as a VARCHAR the formatting is not controlled by your code because you have no FORMAT clause. The formatting is therefore dependent on the view or table definition or the system default. This might well be 'mm/dd/yyyy' (or something else).

 

When the date is displayed in SQLA the date formatting is controlled by SQLA and not by your SQL code. So that might explain the difference between SQLA and BTEQ.

 

Why are you casting your TS columns to DATEs? Why not just use the TS values in the GREATEST function? Is there a specific reason for doing this?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

Hi Dave,

those stupid LEAST/GREATEST functions don't support date/time, at least it's fixed in TD16.10 :-)

 

Instead of VarChar it's better to cast to INT, more efficient and format independent.

Enthusiast

Re: Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

hi Dave,

 

thanks for the quick response!! 

 

Here are the few things:

  1. I tried with format clause but it didn't work even and the result was same.
  2. in my SQL assitant i have my date format set to YYYY-MM-DD. But i think once date has been converted to varchar, this setting doesn't matter anymore. please correct me if am wrong.
  3. Reason for casting to date is we don't want timestamp level comaprison but the date level only. Also we want final output as date only and note in timestamp.
  4. When i am applying GREATEST function without casting TIMESTAMP to DATE but from TIMESTAMP to VARCHAR directly, its working fine.
  5. Another important thing, on trying the same query on other system but on the same database and same tables, result came was correct Smiley Happy.
Enthusiast

Re: Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

Thanks Dnoeth,

I was just waiting and glad to see your reply.

Your suggestion works perfectly but could you please explain the issue with previous approach.

 

thanks,

Sauhard

Senior Apprentice

Re: Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

Note to self- stop playing with latest version of TD Smiley Happy

 

So how about, instead of where you currently have code like:

CAST(COALESCE(CAST(LST_QLFYNG_USE_DTTM AS DATE), DATE '1970-01-01') AS VARCHAR(10))

you use this (change in three places):

CAST(COALESCE(CAST(LST_QLFYNG_USE_DTTM AS DATE), DATE '1970-01-01') AS integer)

At the end your code looks like

CAST(GREATEST(CAST(COALESCE(CAST(LST_QLFYNG_USE_DTTM AS DATE), DATE '1970-01-01') AS integer),
CAST(COALESCE(CAST(LST_REFL_DTTM AS DATE), DATE '1970-01-01') AS integer),
CAST(COALESCE(CAST(LST_RECHRG_DTTM AS DATE), DATE '1970-01-01') AS integer)
) AS DATE) AS FNL_LST_DATE

HTH

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

Hi Sauhard,

 

 

you seem to have different DATEFORM settings in SQLA vs. BTEQ (don't ask why, there are several places to set that: user, connection or session level):

When you cast timestamp -> date -> varchar in SQLA it's using the default INTEGER format with a two digit year (YY/MM/DD) while runnnig the same query uses ANSI format with a four digit year (YYYY-MM-DD). 

When you directly cast the timestamp there's only a single format with a four digit year.

 

And while casting a string to date a two digit year is interpreted as 20th or 21st century based on dbscontrol general field 14: Century Break.

 

 

You can control the default date format using

SET SESSION DATEFORM = ANSIDATE | INTEGERDATE
Enthusiast

Re: Incorrect date is coming while casting DATE column to VARCHAR(10) in GREATEST function

Thanks Dnoeth,

 

Now i am very much clear the issue and i was expecting such kind of thing from the very begining. 

Now keeping the query same, i changed my date/time format in my ODBC connection from IAA to AAA and now in SQL ASSISTANT also i am getting correct result.