locked
Script task - date conversion issue RRS feed

  • Question

  • Hi .

    I am using script task to fetch data from excel and pass it to a datatabale. From the dataTable, i am moving the data to sql table.

    The data in each of the excel is of different format - one having: 19-Apr-2018 00:00:00  , another excel has data as 22/11/2019 00:00:00

    I am trying to check if the data being passed to SQL is correct, before inserting the data. 

    I have tried the below code:

     DateTime date;
     bool isValidBookedDate = true;
     string[] formats = { "yyyy-MM-dd hh:mm tt" };
     foreach (DataRow dtrow in excelDataSet.Tables[0].Rows)
    {
      if (DateTime.TryParseExact(dtrow[5].ToString().Trim(), formats, System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None,out date)) 
                        {                       
                            isValidBookedDate = true;
                        }
                        else
                        {                       
                            isValidBookedDate = false;
                            break;
                        }
    }
    In my SQL table, the smalldatetime column data is of the format: 2014-10-24 00:00:00

    I am getting error in 2 places - One in parse, when the date looks correct, but still the loop goes to isValidbookeddate = false. sometimes if the data passes the parse code, it fails in the Insert  SQL statement with the error : The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value

    The insert code is available like this after the datetime.parse - Insert into TableName ([Booked Date],[Eff Date], TestDate) Values ('22/11/2019 00:00:00','01/11/2019 00:00:00','04/04/2020 06:22:01') . In the SQL table columns, Booked Date,Eff Date are smalldatetime column, TestDate is varchar

    How to work with the different formats of the date, and pass the correct excel dates to table?

    Thanks

    Saturday, April 4, 2020 6:04 AM

All replies

  • In my opinion it may be easier to send this date into varchar column and then try to convert in T-SQL based on each particular pattern. So, you would need to have either intermediate table or an extra column in your source table and from that column eventually convert.

    Does it sound like it may work for you?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, April 5, 2020 2:21 AM
  • HI.

    Can this be done with in the dataTables ? How to handle the strings having the formats - 19-Apr-2018 00:00:00   ,  22/11/2019 00:00:00  and converting them in yyyy-mm-dd hh:mm:ss  smalldatetime format.

    I have tried this as well:

    dtrow[5] = Convert.ToDateTime(dtrow[5].ToString().Trim(), System.Globalization.CultureInfo.InvariantCulture.ToString("MM/dd/yyyy hh:MM:ss"));

    Error : No overload for method 'ToString' takes 1 arguments

    If I remove all parsing ( date time conversion ,  datetime.parse ) and let the data flow, I get error when doing the Insert into statement. Error:

    • The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value

    what am i missing in this?

    Thanks



    • Edited by Venkatzeus Sunday, April 5, 2020 3:48 AM
    Sunday, April 5, 2020 3:08 AM
  • Are you able to just try my suggestion? How big is the file you're loading? My suggestion - don't try to insert into date column (or small date time). Use varchar column to send your date. Once you loaded the data into varchar column, use convert function and filter based on the patterns (e.g. first filter, then use convert).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, April 5, 2020 1:35 PM
  • Hi,

    set dateformat dmy

    or mdy will work for you. You need to load two excel files separately using two separate process. Once loaded just merge two tables. You can format fields in excel too and then load using one process.


    Monday, April 6, 2020 12:21 AM
  • Hi.

    I need to convert the format to MM/dd/yyyy hh:mm:ss

    I get the data in : dd/MM/yyyy hh:mm:ss

    I have tried the below code:

    string[] formats = { "dd/MM/yyyy hh:mm:ss" };
      if (DateTime.TryParseExact(row[5].ToString(), formats, System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None, out date))
       {
     row[5] = Convert.ToDateTime(date.ToString("{MM/dd/yyyy hh:mm:ss}"));   // this line did not work
    }

    What am I missing ? I need a date format which is in MM/dd/yyyy hh:mm:ss format

    Monday, April 6, 2020 3:38 AM
  • Hi Venkatzeus,

    myDate = DateTime.Parse(string.Format(new MyDateTimeFormat(), "{0:yyyyMMddhhmmss}", dateString));

    Hope the following links will be helpful:

      Convert Date In Script Task

      Convert String to Datetime (USING SSIS)

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, April 6, 2020 9:38 AM