Timezone support for Date/Timestamp data types

manish1005 · Post by **manish1005** » Mon Oct 29, 2007 9:09 am

Hi,

I was wondering if there is anyway to extract(or load) records of date/timestamp/time datatypes with *timezone* information. My Database fields for date/time have timezone information associated with them, but when I use datastage timestamp/date datatypes it gets truncated.

Other way round, when I use sequential file to load data into database, sequential file does not recognize timestamp/time datatypes with timezone information(say +5-30).

If I use varchar data type to extract time/timestamp fields in sequential file and then try to convert it into timestamp in Modify stage (before supplying it to ODBC stage) then also I could not find any datastage function that supports timezone information.

ray.wurlod · Post by **ray.wurlod** » Mon Oct 29, 2007 10:49 pm

Can you please provide an example of what you mean? Perhaps reading them as string data types is a good workaround.

manish1005 · Post by **manish1005** » Mon Oct 29, 2007 11:29 pm

Example of data:

"timewithtimezone14","timestampwithouttimezone15","timestampwithtimezone16", "timewithouttimezone17"
"24:00:00-1359","4897-12-31 AD 10:23:54","4897-12-31 AD 13:53:54+05:30","24:00:00",

I can skip the AD/BC part for time being. My database can take input in the format specified above in INSERT SQL queries.

Perhaps reading them as string data types is a good workaround.

If I read then as varchar and load into ODBC Connector stage, first I get schema reconciliation errors. I turned schema reconciliation to No in ODBC connector stage, and now it gives some schema rec. warnings and following error message:

Item #: 15
Event ID: 203
Timestamp:: 2007-10-30 10:55:44
Type: Fatal
User Name: dsadm
Message:: ODBC_Connector_15: Error when checking operator: When binding input interface field "timestampwithouttimezone15" to field "timestampwithouttimezone15": No default type conversion from type "string[max=25]" to type "timestamp".
Error when checking operator: When binding input interface field "timestampwithtimezone16" to field "timestampwithtimezone16": No default type conversion from type "string[max=25]" to type "timestamp".
Error when checking operator: When binding input interface field "timewithouttimezone17" to field "timewithouttimezone17": No default type conversion from type "string[max=12]" to type "time".

ray.wurlod · Post by **ray.wurlod** » Tue Oct 30, 2007 7:10 am

Include CAST functions in your SQL (the derivation columns) to convert to VarChar data type during the extraction phase. This will obviate the warning messages.