All Forums Database
rcotten 2 posts Joined 11/12
06 Nov 2012
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...

dnoeth 4628 posts Joined 11/04
07 Nov 2012

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:


rcotten 2 posts Joined 11/12
09 Nov 2012

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))

09 Apr 2013

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 ) 

Connection test failed.

Cannot load driver: com.teradata.jdbc.TeraDriver
Do i need to place it in Teradata Dev Server?


tomnolan 594 posts Joined 01/08
10 Apr 2013

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

You must sign in to leave a comment.