DSXchange

Hi,
My job is to insert data from file to SQL server.
Below is my Job flow.
Seq file --->transformer --->SQL Server.
I'm reading date as varchar and inserting as Timestamp.
Eg for date: '8/29/2011 5:26:03AM'

1. when I tried to insert without any conversion, below error is I'm getting

odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur.

2. when I tried to insert after convering varchar to Timestamp, below is are the warnings i'm getting.

APT_CombinedOperatorController,2: Data string '8-29-2011 5:26:03AM' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': the value for tag %yyyy has fewer characters than expected.

Could you please help me to fix this issue?

Regards,

You need to tell DS what your timestamp from the source looks like...

The format for '8-29-2011 5:26:03AM' is not '%yyyy-%mm-%dd %hh:%nn:%ss'

Thanks Sussen. But I tried converting to Timestamp format: '%yyyy-%mm-%dd %hh:%nn:%ss'. Below are the warnings i'm getting.

odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur.

odbc_inc_stg: Error when checking operator: When binding input interface field "inc_sys_created_on" to field "inc_sys_created_on": No default type conversion from type "string[max=25]" to type "timestamp[microseconds]".


Regards,

Can anybody help me out?

Regards

Probably. Be patient.

The clue you're looking for is in suse_dk's answer:

You need to tell DS what your timestamp from the source looks like...

The format for '8-29-2011 5:26:03AM' is not '%yyyy-%mm-%dd %hh:%nn:%ss'

When converting to a timestamp, you need to specify the format of the string you are converting from...that's the format that DataStage needs to be told about. This topic has been covered many times in the forum, and you can also look in the Parallel Job Developer's Guide for more information on time and date format strings.

Regards,

ajithaselvan wrote:Can anybody help me out?

Groucho Marx wrote:I'd love to help you out - which way did you come in?

as per suse_dk and james, I tried converting to Timestamp format: '%yyyy-%mm-%dd %hh:%nn:%ss'. Below is the warnings i'm getting.

odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur

For me it looks like you are trying to put a varchar into a datetime... where exactly are you doing your type conversion? - in a transformer?

String to timestamp is not an implicit conversion - you will need to specify a conversion function in a Modify or Transformer stage to achieve a data type of Timestamp.

I'm doing the type conversion in Transformer.
Source:8/29/2011 5:28:51AM

below is the conversion logic

StringToTimestamp(lnk_input_to_filter.inc_sys_created_on,"%mm/%dd/%yyyy %hh:%nn:%ss")

Warning:
xfrm_on_lan_id,1: Data string '8/29/2011 5:28:51A' does not match format '%mm/%dd/%yyyy %hh:%nn:%ss': the value for tag %mm has fewer characters than expected.

suse_dk wrote:For me it looks like you are trying to put a varchar into a datetime... where exactly are you doing your type conversion? - in a transformer?

I'm doing the type conversion in Transformer.
Source:8/29/2011 5:28:51AM

below is the conversion logic

StringToTimestamp(lnk_input_to_filter.inc_sys_created_on,"%mm/%dd/%yyyy %hh:%nn:%ss")

Warning:
xfrm_on_lan_id,1: Data string '8/29/2011 5:28:51A' does not match format '%mm/%dd/%yyyy %hh:%nn:%ss': the value for tag %mm has fewer characters than expected.

You don't have two digits in the month but your format string says that you do.

Do learn to read what the error messages are telling you.

I understand the error message. If my input is 08/29/2011 05:28:51AM,
I will not be getting any problem in inserting.
But my source is 8/29/2011 5:28:51AM. If I add leading zero to month (as 08), again there is possibility to get same warning for %hh also as source does not have 2 digits in the format.
moreover I may get source value as 12/29/2011 15:28:51AM.

How to handle both the cases?

Documentation is a wonderful thing. The "s" option allows leading spaces in date and/or time formats. For example:
Would handle single digits in the month, day, hour, minute and second. You can also find discussions on how to handle the "am/pm marker" there.