Teradata Studios Default Time Display

Teradata Studio
Enthusiast

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

12 REPLIES
Teradata Employee

Re: Teradata Studios Default Time Display

Sean, Time is returned as a long. You can cast it as a Varchar, for example:

select (cast(time as varchar(8));

Teradata Employee

Re: Teradata Studios Default Time Display

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.

Enthusiast

Re: Teradata Studios Default Time Display

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.

Teradata Employee

Re: Teradata Studios Default Time Display

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.

Enthusiast

Re: Teradata Studios Default Time Display

when using current_time however it comes out weird 

eg

16:23:06+00:00 

is a preference thing? 

Teradata Employee

Re: Teradata Studios Default Time Display

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?

Enthusiast

Re: Teradata Studios Default Time Display

yes it is

Teradata Employee

Re: Teradata Studios Default Time Display

Hi Francine,

I think that adding a preference for time values in the Result Set Viewer should be very usefull.

Thanks

Francesco

Teradata Employee

Re: Teradata Studios Default Time Display

I have opened a JIRA for this improvement.