BULK MSSQL IMPORTING DATE-ERROR

Five Stars

BULK MSSQL IMPORTING DATE-ERROR

I use this job to import data from a csv file into a bulk mssql component, I got an error on a date field in the table because the date is less than 1-1-1753, so I used an output file to catch the rejected lines but it doesn't catch any erroneous date.

 

any solution?


Accepted Solutions
Employee

Re: BULK MSSQL IMPORTING DATE-ERROR

Hi,

 

     Please use the below filter expression in tmap to filter older dates.

image.png

 

TalendDate.compareDate(row1.data,TalendDate.parseDate("dd-MM-yyyy","01-01-1753")) == 1

 

I was able to segregate the data using this condition.

 

If the update has helped you, could you please mark the topic as solution provided?

 

Warm Regards,

 

Nikhil Thampi


All Replies
Ten Stars

Re: BULK MSSQL IMPORTING DATE-ERROR

It is a MSSQL error not a java error.
I believe the smallest date in java is Sun, 2 Dec 292269055 BC 16:47:04 +0000

Since the minimum date value that a DATETIME data type can have is January 1, 1753, you have to use either INT, DECIMAL/NUMERIC or VARCHAR data type to hold a date value before January 1, 1753. Of these three data types, the best data type to use is the INT data type because it will only require 4 bytes to store the data. DECIMAL or NUMERIC data type will require 5 bytes to store the date value in YYYYMMDD format while a VARCHAR data type will use 8 bytes.
Five Stars

Re: BULK MSSQL IMPORTING DATE-ERROR

THANK YOU SO MUCH FOR YOUR ANSWER,

but actually I want to reject dates before  1-1-1753,I don t want to keep them so I used the output file to reject them.

but I got error and the file doesn t catch errorenous rows.

 

even I use Tfilter it generates errors.

Highlighted
Ten Stars

Re: BULK MSSQL IMPORTING DATE-ERROR

If you talk about errors, please attach it...
Suggest using a tMap with 2 outputs and filter.

For the sake of argument... Why on earth you want to reject these records, because of technical mssql limitations?
Employee

Re: BULK MSSQL IMPORTING DATE-ERROR

Hi,

 

     Please use the below filter expression in tmap to filter older dates.

image.png

 

TalendDate.compareDate(row1.data,TalendDate.parseDate("dd-MM-yyyy","01-01-1753")) == 1

 

I was able to segregate the data using this condition.

 

If the update has helped you, could you please mark the topic as solution provided?

 

Warm Regards,

 

Nikhil Thampi

Five Stars

Re: BULK MSSQL IMPORTING DATE-ERROR

Thank you so much sir.
Employee

Re: BULK MSSQL IMPORTING DATE-ERROR

Happy to help you.

 

Enjoy programming in Talend :-)

 

Warm Regards,

 

Nikhil Thampi

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download