How to load Date columns in consistent manner

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » How to load Date columns in consistent manner...

How to load Date columns in consistent manner

Rate Topic

Display Mode

Topic Options

Author

Message

sqlstud

Posted Wednesday, January 16, 2013 10:15 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237

Hi,

I am loading the table from Excel source using SSIS 2005.

Excel contains one of the columns as Exp_Date and its examples values are

10/09/2010 17:00:51
11/09/2010 11:45:16
11/09/2010 11:45:16
13/09/2010 10:07:59
13/09/2010 10:07:59
13/09/2010 10:07:59
14/09/2010 10:44:59
15/09/2010 10:44:59
16/09/2010 14:19:25

In table, for some columns it is loading fine. But for some columns it is loading in an inconsistent manner, i.e. a date that should be loading as ‘10/09/2010’ is actually loading as ‘09/10/2010’.

Please advise how to load the date column values into table as it is in Excel file.

Regards
SqlStud

Post #1408142

opc.three

Posted Thursday, January 17, 2013 8:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735, Visits: 11,788

sqlstud (1/16/2013)

Hi,

I am loading the table from Excel source using SSIS 2005.

Excel contains one of the columns as Exp_Date and its examples values are

10/09/2010 17:00:51
11/09/2010 11:45:16
11/09/2010 11:45:16
13/09/2010 10:07:59
13/09/2010 10:07:59
13/09/2010 10:07:59
14/09/2010 10:44:59
15/09/2010 10:44:59
16/09/2010 14:19:25

In table, for some columns it is loading fine. But for some columns it is loading in an inconsistent manner, i.e. a date that should be loading as ‘10/09/2010’ is actually loading as ‘09/10/2010’.

Please advise how to load the date column values into table as it is in Excel file.

Regards
SqlStud

Which driver are you using? The built-in driver? Please post the connection string so we can see the properties? Post the Excel file as an attachment to this thread as well.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1408469

mmartin1

Posted Wednesday, March 20, 2013 11:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:53 AM
Points: 194, Visits: 643

You can import the data as a string and in the dataflow manipulate the string in a derived column transformation to put it into yyyymmdd format, and append that to the time portion.

This is the universal standard for date string and will import nicely into your datetime column, of your destination.

Post #1433363

« Prev Topic | Next Topic »

Permissions