Date Format In Teradata SQL Assistant

Tools
KM
Enthusiast

Date Format In Teradata SQL Assistant

Hi
the
select date1(format 'yy/mm/dd') from testuser.test3;
always yeilding the output 2006-01-01 when it should display '06/01/01'.
I have tried with all sorts of combination , but result is the same.

In BTEQ it's working fine . Could someone please tell me , where I am going wrong . I reckon some parameter specific to Queryman required to be changed but can't figure out .
Thanks in advance.
Kaushik
5 REPLIES
Enthusiast

Re: Date Format In Teradata SQL Assistant

BTEQ adheres to any formatting that you put in your SQL, while ODBC passes back data in the native data type. So, in order to see the result of any formatting that you do when going through ODBC, you need to cast the data type to character after formatting it. Here is an example:

select date1(format 'yy/mm/dd') (char(8)) from testuser.test3;

This should give you the right format.

There may be another way to have dates always formatted as 'yy/mm/dd' when going through ODBC through settings in ODBC or on your PC, but casting it to character will insure that you get the format that you want, regardless of those settings.
Enthusiast

Re: Date Format In Teradata SQL Assistant

Hi

The above reply is correct, and you could also use the CAST function.

select CAST((date1 (format 'yy/mm/dd')) as char(8)) from testuser.test3;
Enthusiast

Re: Date Format In Teradata SQL Assistant

I believe BTEQ uses Native connections while SQL assistant use ODBC.
I am not sure if we can change the ODBC settings to follow ANSI date format "AAA".

I have not tried if it changes.

We can probably check this.

Vinay
N/A

Re: Date Format In Teradata SQL Assistant

How do you display last row (record) from a table.

Given this table

Name      create_tmp

AA          09-11-2009 01:02:00

AA           09-11-2011 01:02:00

AA           09-11-2012 01:12:09

AA           09-11-2013 01:02:59

BB           09-11-2010 01:02:00

BB          09-11-2011 10:02:10

CC          09-15-2012 01:02:00

Disired result

Name       Create_time

AA           09-11-2013 01:02:59

BB          09-11-2011 10:02:10

CC          09-15-2012 01:02:00

Thanks in advance.

Enthusiast

Re: Date Format In Teradata SQL Assistant

Looks like the last row is always the latest record of the 'name' column. In that case, it would be like below.

Select Name, max(create_time)

If that is not the case then you can use ROW_NUMBER() and give number to all the rows and use max of it by name to get the last record.