All Forums Teradata Studio
Sean_Kelly 13 posts Joined 03/14
24 Feb 2016
Teradata Studios Default Time Display

Hi All,
How do I change the default display of a time field in Teradata Studios;
when I do a select time I get the following result;
141119
I want it to be viewed like;
14:11:19
 
Regards Sean

fgrimmer 553 posts Joined 04/09
24 Feb 2016

Sean, Time is returned as a long. You can cast it as a Varchar, for example:
select (cast(time as varchar(8));

tomnolan 594 posts Joined 01/08
24 Feb 2016

The Teradata Database Reference / "SQL Functions, Operators, Expressions, and Predicates" says the following: 
 
TIME versus CURRENT_TIME
TIME is deprecated. Use the ANSI SQL:2011 compliant CURRENT_TIME function instead.
 
The issue is that the deprecated TIME function returns a FLOAT value, which needs to be CAST to VARCHAR as Francine noted, in order to obtain a formatted time display.
 
In contrast, the CURRENT_TIME function returns a SQL TIME value, so the proper time formatting will occur automatically.
 

Sean_Kelly 13 posts Joined 03/14
26 Feb 2016

Thank you both, any ideas on this becoming a prefence which can be altered by the user in the future?
I do a select statement before doing inserts to check the data before going through with the statement, from what you guys are telling is to see the time correctly I always have to cast the time as varchar? that rather annoying considering SQL Assistant always bought back the current time formatted.
 
 

tomnolan 594 posts Joined 01/08
26 Feb 2016

My recommendation is that you use the CURRENT_TIME function instead of the TIME function. The Teradata Database documentation indicates that the TIME function is deprecated, so you should stop using it, and switch to using the CURRENT_TIME function.

Sean_Kelly 13 posts Joined 03/14
26 Feb 2016

when using current_time however it comes out weird 
eg
16:23:06+00:00 
 
is a preference thing? 

fgrimmer 553 posts Joined 04/09
26 Feb 2016

There isn't currently a preference in Studio for removing the time zone when displaying Time values. There is an option to format Dates in the Result Set Viewer. We could add a preference for time values as well. Is that what you are looking for?

Sean_Kelly 13 posts Joined 03/14
15 Mar 2016

yes it is

Francesco 1 post Joined 05/09
16 Mar 2016

Hi Francine,
I think that adding a preference for time values in the Result Set Viewer should be very usefull.
 
Thanks
Francesco

Francesco Golluscio

Professional Service
Teradata Italia S.r.l.
Viale Erminio Spalla, 41/C
00142 Roma, Italia
+39.348.2601019 – mobile
francesco.golluscio@teradata.com

______________________________________________

fgrimmer 553 posts Joined 04/09
16 Mar 2016

I have opened a JIRA for this improvement.

Sean_Kelly 13 posts Joined 03/14
17 Mar 2016

thank you guys, keep me posted  please :)

Sean_Kelly 13 posts Joined 03/14
08 Apr 2016

Is there any update there any update on this?

fgrimmer 553 posts Joined 04/09
08 Apr 2016

We have a JIRA opened on this. Our current release, 15.11, is already in final testing. We hope this issue will be resolved in the next release.

You must sign in to leave a comment.