All Forums Tools
KM 5 posts Joined 02/06
14 Feb 2006
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

Barry-1604 176 posts Joined 07/05
15 Feb 2006

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.

XTUPIE 42 posts Joined 11/05
23 Feb 2006

Hi The above reply is correct, and you could also use the CAST CAST((date1 (format 'yy/mm/dd')) as char(8)) from testuser.test3;


BBR2 96 posts Joined 12/04
05 Mar 2006

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

ZQkal 1 post Joined 03/13
18 Mar 2013

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.

KS42982 137 posts Joined 12/12
19 Mar 2013

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.

You must sign in to leave a comment.