Err:- Invalid value <Month: 20> for date <2014-10-17> DS4.2

[former_member214617]

hi guys

I have xml source file from which i am extracting the data and storing in SQL Server 2012 table, for this before extracting the data i am saving the xml source file in UTF-8 format so that special characters in xml file should not give errors...

there is one data column in xml file which is working fine but when the date is in october month then i am getting below mentioned warning and data going as NULL is sql table column

ERROR

Invalid value <Month: 20> for date <2014-10-17>.Invalid value <Month: 20> for date <14/20/2000>

i have tried changing the date format

• to_char(column_name, 'dd/mm/yyyy')
• to_date (to_char(column_name, 'dd/mm/yyyy'), 'dd/mm/yyyy')

In source xml file column is of date format and when in target table column was also data type then i was getting an error and job was getting failed so i tried to convert the same but it end up with warning and going NULL in the column...

I got one same issue from another user on another forum which is saying may be it is the language issue.. if it so could you please help me how to resolve same..

Data services 4.2

regards

[Former Member]

Hi Leo,

I have had a similar issue with the use of the to_date and to_char functions.  If you change the source field to be of type varchar instead of date or datetime and then use the cast function to cast the varchar field as a date or datetime, you may fair better - this has worked for me recently.

[former_member211387]

Hi

If the date you are trying to convert is "2014-10-17" then you should use

to_char(column_name, 'yyyy-mm-dd')  or

to_date(to_char(column_name, 'yyyy-mm-dd')

kind regards

Raghu

[former_member214617]

hi

I tried the solution you gave... but i am getting same warning and NULL going in table column..

Help please

Regards

[former_member211387]

Hi

Is the date definitely in the format 2014-10-17 ?

Are you loading this data into a field of datatype varchar or date ?

try to_char(to_date(colume_name, 'yyyy-mm-dd')) in case of loading into a varchar field.

if you are loading into a field of datatype date then try using to_date(column_name, 'yyyy-mm-dd')

kind regards

Raghu

[former_member214617]

hi

Date is coming in the format 2014-10-07, problem i am getting only for the dates which comes in october month.. if it is 2014-03-01 or anything except october dates.. it is working fine...

I tried again by chaing the datatype of column as per your suggestion, but still i am getting same warning message and NULL value in column

[former_member187605]

By default, when the definition of your date field in the xsd file is in the form <xsd:element name="date_field" type = "xsd:date"/>, xml dates are in the form 'YYYY-MM-DD'. Xml dates are normally picked up by DS as such and copied straight into a target field of type date without having to specify any conversion.

You haven't mentioned the original error you ran into. I cannot reproduce your problem.

[former_member211387]

Hi,

Is this issue resolved? If so can you let us know ?

If it is not resolved yet, please send a screenshot of the xsd and the error message that you are getting?

kind regards

Raghu