none
Convert an integer value that was created with the Excel DATEVALUE formula to a valid date? RRS feed

  • Question

  • Hello everyone,

    How can I convert an integer value that was created with the Excel DATEVALUE formula to a valid date in SSIS?

    Is this even possible?

    For example:

    =DATEVALUE("8/22/2008") will format the cell to display 39682.

    Reading the column as a string to get the int value (39682) - how can I turn this into a valid date using SSIS and then importing the real date to sql server?

    Thank you!

    Wednesday, April 9, 2014 12:52 PM

Answers

  • So the excel datevalue returns the number of days since 1/1/1900.

    So here is how you could get a date back from the number (considering the 1/1/1900 as 1)

    select dateadd(day,41739-2,'19000101')-- Getdate()
    select dateadd(day,39682-2,'19000101') --8/22/2008
    
    You can easily have this expression in a derived column 

    Satheesh
    My Blog | How to ask questions in technical forum


    Thursday, April 10, 2014 5:27 AM

All replies

  • You can use Script component for this and convert your integer values to Date. An example here is following:

    CultureInfo provider = CultureInfo.InvariantCulture;
    string dateString = "08082010";
    string format = "MMddyyyy";
    DateTime result = DateTime.ParseExact(dateString, format, provider);

    Source: http://stackoverflow.com/questions/2441405/converting-8-digit-number-to-datetime-type


    Vikash Kumar Singh || www.singhvikash.in


    Wednesday, April 9, 2014 1:55 PM
  • A much better solutions is mention here:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/246040d7-f78c-413d-8647-5b5ddc4b0dda/ssis-with-excel-and-cells-with-dates?forum=sqlintegrationservices

    You need to change your connection string as following: 

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=V:\dates.xls;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1;IMPORTMIXEDTYPES=TEXT";

    Try it and let us know


    Vikash Kumar Singh || www.singhvikash.in

    Wednesday, April 9, 2014 2:01 PM
  • @Vikash

    I don't think this is the right way for me to go.

    I found

    DateTime dt = DateTime.FromOADate(39682);

    via a script task should do but can this be done in a derived column without the need of a script task?

    Source: Stackoverflow - Excel Date

    Thursday, April 10, 2014 5:11 AM
  • So the excel datevalue returns the number of days since 1/1/1900.

    So here is how you could get a date back from the number (considering the 1/1/1900 as 1)

    select dateadd(day,41739-2,'19000101')-- Getdate()
    select dateadd(day,39682-2,'19000101') --8/22/2008
    
    You can easily have this expression in a derived column 

    Satheesh
    My Blog | How to ask questions in technical forum


    Thursday, April 10, 2014 5:27 AM