JDBC - create timestamp from LogonDate and LogonTime

Database

JDBC - create timestamp from LogonDate and LogonTime

Very new to Teradata but have searched the forums and tried many different technicques and can not get this query to run in either SQL Assistant or via the JDBC driver v14.  (Ideally I want this to work via the JDBC driver).

Select cast(LogonDate as timestamp(0)) + (LogonTime - time '00:00:00' hour to second) TS,

UserName, UserGroup, UserDescription, DatabaseName, TableName, StatementText

from dp_mcif_hk_view.AccessLog_view

where TS >= ?

Order by TS desc

A select * in sql assist shows:

LogonDate    LogonTime  UserName   UserGroup  UserDescription  DatabaseName  TableName  StatementText

31/10/2012   13:14:01    HK123         U_Grp_1     Blah to blah        DP_HK_Name     Name           Sel * fom blah

The error I keep getting is "SELECT files: 5407: Invalid operation on an ANSI Datetime or Interval Value."

I need a Timestamp created out of LogonDate and LogonTime in format "YYYY-MM-DDBHH:MI:SS". Im trying to assign that to TS.

the ? in the where clause will be a timestamp field that I pass in my java program. But right now I can't even get this to work via an ODBC connection with SQL Assist. (I have change the Date/Time in ODBC to AAA).

Appreciate any guidance here...

Ray

4 REPLIES
Senior Apprentice

Re: JDBC - create timestamp from LogonDate and LogonTime

Hi Ray,

check the actual datatypes.

This looks like a table with data from the AccessLog and within that table the LogonTime is a FLOAT:

CAST(LogonDate AS TIMESTAMP(0)) + CAST(TRIM(LogonTime) AS INTERVAL HOUR TO SECOND(2))

Dieter

Re: JDBC - create timestamp from LogonDate and LogonTime

Thank you... I think that has it working for me.  I ended up having to provide a format but that is good.

CAST(LogonDate AS TIMESTAMP(0) Format 'yyyy-mm-ddbhh:mi:ss) + CAST(TRIM(LogonTime) AS INTERVAL HOUR TO SECOND(2))

Enthusiast

Re: JDBC - create timestamp from LogonDate and LogonTime

Hi Dieter,

/* create New JDBC Data source for Teradata database in weblogic console*/

I downloaded JDBC Driver from teradata website and placed the jar files in my local system where i installed TTU but  NOT in server ( its not working ) 

Message icon - Error Connection test failed.

Message icon - Error Cannot load driver: com.teradata.jdbc.TeraDriver

Do i need to place it in Teradata Dev Server?

Teradata Employee

Re: JDBC - create timestamp from LogonDate and LogonTime

Mahesh, please start a new topic in the Connectivity forum for your issue. Please do not post problems to unrelated topics. Thanks.