An Overflow Occurred While Converting to Datetime in SQL Compact Edition 3.5

Question

Hi Friends,

 I am using SSIS to fetch the records from Oracle into SQL Compact Edition 3.5. When I fetch some records from Oracle say for e.g. 1/1/0001 12:00:00 into SQL CE, it throws me an error "An Overflow occurred while converting to datetime".

Can You suggest me how I can resolve this datetime issue in SSIS ?

Many Thanks in Advance!!

Regards,

Bhavik Mandaliya

Answer 1

Hello Bhavik,

The datatime datatypes supports only dates between the years 1753 and 9999, you can't store dates outside of this range like year 0001. You would have to change it to a valid date or to NULL value.

---

Olaf Helper

Blog Xing

Answer 2

Hi Olaf,

Thanks for your immediate response. 

In Database there so many records which has this unusual year so we cannot go for this valid date option.

However can you suggest, how we can achieve by changing it to NULL Value from SSIS perspective.

Regards,

Bhavik 

Answer 3

Hello Bhavik,

One way would be to modify the Oracle to return NULL value when the date is below year 1753, otherwise the date data.

---

Olaf Helper

Blog Xing

Answer 4

Thanks Olaf !!