Importing Oracle date type to SQL date type error

Question

Importing Oracle date type to SQL date type error

yashuvp

Right there with Babe

Points: 744
More actions
January 16, 2014 at 7:55 pm

#288431

I am importing Oracle table with data using SSIS package and while preview the data I am getting following error.
Year, Month, and Day parameters describe an un-representable DateTime. (mscorlib).
Any suggestion?
Thanks,
Yash

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

How does the query look like? Do you use any parameters?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

yashuvp Right there with Babe Points: 744 More actions · Answer 2

I am using default settings of the SSIS Package utility.

Thanks,

Yash

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

You are using the Import/Export wizard?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

yashuvp Right there with Babe Points: 744 More actions · Answer 4

yashuvp

Right there with Babe

Points: 744

January 17, 2014 at 8:18 am

#1681091

Yes.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

yashuvp (1/16/2014)
I am importing Oracle table with data using SSIS package and while preview the data I am getting following error.
Year, Month, and Day parameters describe an un-representable DateTime. (mscorlib).
Any suggestion?
Thanks,
Yash

If these are separate columns that you are pulling together to form a date, sounds like that the data is bad.

yashuvp Right there with Babe Points: 744 More actions · Answer 6

I found couple of data that are in wrong format 1/1/0001 (old data) so I have modified the sql to look for the date that are good, but still I am getting the error.

Here is the sql that I am using to pull the data.

SELECT

ID,

CODE,

CD,

TYPE,

PROJECTED_AMOUNT,

PCS_ID,

PROCESS_TIME_CODE,

PROPOSAL_STATUS_CODE,

BEGIN_DATE, TO_CHAR(BEGIN_DATE, 'MM/DD/YYYY')

FROM TABLE_A

WHERE > 70000

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

yashuvp (1/17/2014)
I found couple of data that are in wrong format 1/1/0001 (old data) so I have modified the sql to look for the date that are good, but still I am getting the error.
Here is the sql that I am using to pull the data.
SELECT
ID,
CODE,
CD,
TYPE,
PROJECTED_AMOUNT,
PCS_ID,
PROCESS_TIME_CODE,
PROPOSAL_STATUS_CODE,
BEGIN_DATE, TO_CHAR(BEGIN_DATE, 'MM/DD/YYYY')
FROM TABLE_A
WHERE > 70000

Verify the dateformat setting for your SQL Server instance. If it is set to DMY, this could be the issue.

yashuvp Right there with Babe Points: 744 More actions · Answer 8

Could you please tell me where can I see for that setting?

Thanks,

Yash

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Lynn Pettis

SSC Guru

Points: 442467

January 17, 2014 at 10:58 am

#1681181

Try this in SSMS:

sp_helplanguage @@language

yashuvp Right there with Babe Points: 744 More actions · Answer 10

It's mdy for English. How can I change to mm/dd/yyyy? Where?

Thanks,

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

yashuvp (1/17/2014)
It's mdy for English. How can I change to mm/dd/yyyy? Where?
Thanks,

Actually, that is what you want.

You will need to analyze the data to see if there are dates that aren't dates.

Also, it may also help if you showed us how your transform is being accomplished. Since we can't see what you see it is hard to provide you much more than guesses.

yashuvp Right there with Babe Points: 744 More actions · Answer 12

Thanks for your input. I looked at the table and found few corrupted data in wrong format. I exclude those and now I am able to import data into the SQL server. Thanks for your guidance and input, much appreciated.

Thanks,

Yash