strange display format when using TD Studio

Teradata Studio
Highlighted

strange display format when using TD Studio

Hi,

We are deploying TD Studio to users. In test environment, we set JDBC connection properties as TNANO=1.

Below is the information I got from manual (in italic)

 

TNANO is the fractional seconds precision for the time datatype. If this
value is set, then a preparedStatement.setTime() results in a time with
the specified precision. For example, if TNANO is set to 3, then 10:02:30
is sent to the database as 10:02:30.000. This the same with TSNANO,
except that it is used for timestamps.
TNANO and TSNANO parameters support the Default Connection for
Java External Stored procedures.
If using stored procedures with time or timestamp parameters, then
these URL parameters are to match the fractional second precision of
the stored procedure parameters.
Example URL jdbc:teradata: //cs4300s1/TSNANO=6,TNANO=3

 When I ran the query "Select time", I got the result as below:

 

                                          103849.29

 

Whereas when using TD SQL Assistant, I got the below result:

 

                                            10:19:22

Can you please explain for us with this strange display when using TD Studio?

 

Thank you.

Vianh Cao

4 REPLIES
Junior Contributor

Re: strange display format when using TD Studio

  1. Select time is not a Prepared Statement
  2. the datatype of time is not TIME, but INTEGER with a FORMAT '99:99:99' 

Try Select current_time(2) instead, but be aware that there's an ODBC limitation:

Time fields do not display fractional seconds or Time Zone information.

 

Re: strange display format when using TD Studio

Thanks very much for your assistance with this. 

 

However, I am still wondering why via TD sql assistant it display in different format when I use the same command, ie selec time.

 

Can you please try to verify the difference?

 

Regards, 

Vianh

Junior Contributor

Re: strange display format when using TD Studio

TIME is very old deprecated syntax, SQL Assistant simply knows about it and applies a format, while Studio doesn't and applies the standard format for an INT. You can apply TRIM(TIME) to the same display (but then it's a string not an int).
Or you simply use CURRENT_TIME instead :-)
Teradata Employee

Re: strange display format when using TD Studio

The Teradata JDBC Driver's TNANO and TSNANO connection parameters govern how the Teradata JDBC Driver transmits Time and Timestamp values to the Teradata Database.

 

The TNANO and TSNANO connection parameters have no effect on Time and Timestamp received from the Teradata Database.