I wish to cast string to time. I have three string columns, i have concatanated it in one string as casted it as time; it work fine with select only. But When i wish to insert in to a table using select it gives error 5407. Please find the piece of code below:
insert into dbname.tablename(connection_time)
Select Cast ( Ltrim(Rtrim(msg.con_hour)) || ':' || Ltrim(Rtrim(msg.con_min)) || ':' || Ltrim(Rtrim(msg.con_sec)) AS time ) AS con_time, from message msg
Can someone help me rectify this. Data type for connection_time is TIME(6).
LTRIM and RTRIM are not Teradata functions. Do you really need to TRIM the values? If so, you can use ANSI TRIM(BOTH FROM col)
But the fact that LTRIM and RTRIM appear to work tells me you are using ODBC and allowing the driver to "parse" the query. I suspect the driver is also quietly changing your TIME datatype to numeric because the middle letter in your DateTimeFormat driver option is 'I'.
If you are using SQL Assistant, you can un-check "Allow use of ODBC extensions" (or if you are using some other tool you can "Disable parsing" in the ODBC data source configuration options). That way the driver won't break TIME datatypes - but LTRIM/RTRIM will get an error, just as they would if submitted via some non-ODBC tool such as BTEQ.
If you believe that allowing the driver to modify what you typed and submit a different request to the database is sometimes a good thing, you could leave extensions/parsing options alone and just change DateTimeFormat to 'AAA' or at least some option that has 'A' as the middle letter.