Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to load Date columns in consistent manner Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 10:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, January 17, 2013 8:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
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
Posted Wednesday, March 20, 2013 11:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:20 PM
Points: 220, Visits: 695
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 »

Add to briefcase

Permissions Expand / Collapse