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.
Solved! Go to Solution.
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?
thanks for the quick response!!
Here are the few things:
I was just waiting and glad to see your reply.
Your suggestion works perfectly but could you please explain the issue with previous approach.
Note to self- stop playing with latest version of TD
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
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
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.