SqlBulkCopy Time DataType Issue

Question

How can I bulkcopy a time into a SQL Server database table?

For example I am importing an Excel sheet into my application, and one of the fields is Start Time and the data which is mapped to it is 10:30:00 but when it imports it into the table it displays 30/12/1899 10:30:00. How can I get rid of the date part?

I have tried changing the formats in the database, to Time, varchar, DateTime but all come out the same.

string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

switch (extension)
{
    case ".xls": //Excel 97-03
        conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
        break;

    case ".xlsx": //Excel 07 or higher
        conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
        break;
}

conString = string.Format(conString, excelPath);

using (OleDbConnection excel_con = new OleDbConnection(conString))
{
    excel_con.Open();
    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();

    DataTable dtExcelData = new DataTable();

    //It is recommended as otherwise the data will be considered as String by default.
    dtExcelData.Columns.AddRange(new DataColumn[18] { 
         new DataColumn("Name of Staff", typeof(string)),
         new DataColumn("Host Key of Staff", typeof(int)),
         new DataColumn("Name of Department", typeof(string)),
         new DataColumn("Description of Preferred Zone of Staff", typeof(string)),
         new DataColumn("User Text 2 of Staff", typeof(string)),
         new DataColumn("Name of Programmes of Study of Module", typeof(string)),
         new DataColumn("Description of Programmes of Study of Module", typeof(string)),
         new DataColumn("Host Key of Locations", typeof(string)),
         new DataColumn("Host Key of Module", typeof(string)),
         new DataColumn("Description of Module", typeof(string)),
         new DataColumn("Name", typeof(string)),
         new DataColumn("Scheduled Start Date", typeof(string)),
         new DataColumn("Teaching week pattern of Scheduled Activities as end date", typeof(string)),
         new DataColumn("Section ID", typeof(string)),
         new DataColumn("Description of Type", typeof(string)),
         new DataColumn("Scheduled Start as day name", typeof(string)),
         new DataColumn("Scheduled Start as start time", typeof(string)),
         new DataColumn("Scheduled Finish as end time", typeof(string)),
     });

     using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
     {
         oda.Fill(dtExcelData);
     }

     excel_con.Close();

     string consString = ConfigurationManager.ConnectionStrings["PayrollPlusConnectionString"].ConnectionString;

     using (SqlConnection con = new SqlConnection(consString))
     {
         using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
         {
             //Set the database table name
             sqlBulkCopy.DestinationTableName = "dbo.Data";

             // Map the Excel columns with that of the database table
             sqlBulkCopy.ColumnMappings.Add("Name of Staff", "NameOfStaff");
             sqlBulkCopy.ColumnMappings.Add("Host Key of Staff", "HostKey");
             sqlBulkCopy.ColumnMappings.Add("Name of Department", "Department");
             sqlBulkCopy.ColumnMappings.Add("Description of Preferred Zone of Staff", "Campus");
             sqlBulkCopy.ColumnMappings.Add("User Text 2 of Staff", "Grade");
             sqlBulkCopy.ColumnMappings.Add("Name of Programmes of Study of Module", "AOS");
             sqlBulkCopy.ColumnMappings.Add("Description of Programmes of Study of Module", "AOSDescription");
             sqlBulkCopy.ColumnMappings.Add("Host Key of Locations", "Room");
             sqlBulkCopy.ColumnMappings.Add("Host Key of Module", "ModuleAOS");
             sqlBulkCopy.ColumnMappings.Add("Description of Module", "ModuleDescription");
             sqlBulkCopy.ColumnMappings.Add("Name", "Activity");
             sqlBulkCopy.ColumnMappings.Add("Scheduled Start Date", "StartDate");
             sqlBulkCopy.ColumnMappings.Add("Teaching week pattern of Scheduled Activities as end date", "EndDate");
             sqlBulkCopy.ColumnMappings.Add("Section ID", "SectionID");
             sqlBulkCopy.ColumnMappings.Add("Description of Type", "Type");
             sqlBulkCopy.ColumnMappings.Add("Scheduled Start as day name", "Day");
             sqlBulkCopy.ColumnMappings.Add("Scheduled Start as start time", "StartTime");
             sqlBulkCopy.ColumnMappings.Add("Scheduled Finish as end time", "EndTime");

             con.Open();
             sqlBulkCopy.WriteToServer(dtExcelData);
             con.Close();

             lblStatus.Text = "Upload Successful";
        }
    }
}

what value does your DataTable dtExcelData have when u debug it for starttime field — prasy, Apr 30 '15 at 14:43
30/12/1899 10:30:00 Dont know where it is getting the date from the field in spreadsheet just has 10:30:00 — Alexandria, Apr 30 '15 at 14:49
Just because the spreadsheet only shows 10:30:00 that does not mean that there is no date part behind the scenes, it is just not showing it to you. — Scott Chamberlain, Apr 30 '15 at 14:56
oh right ok thanks @scott Chamberlain, how can I put it into database leaving out date part? — Alexandria, Apr 30 '15 at 15:01
try typeof(DateTime) instead of new DataColumn("Scheduled Start as start time", typeof(string)) and get only "HH:mm:ss" — prasy, Apr 30 '15 at 15:14
prasy like this: new DataColumn("Scheduled Start as start time",typeof(DateTime)"HH:mm:ss"), coming up with an error. — Alexandria, Apr 30 '15 at 15:34
@Alexandria: here is [link](http://stackoverflow.com/questions/3209829/is-it-possible-to-format-a-date-column-of-a-datatable) , first add the extension method as shown to your code and use the below code `dtExcelData.Columns["Scheduled Start as start time"].Convert( val => DateTime.Parse(val.ToString()).ToString("HH:mm:ss));` and try to keep the same Datatype which u have it now `typeof(string))` — prasy, Apr 30 '15 at 16:33
prasy I have added the extension method from link do I have to change anything in it like column name or anything? Also when I add your code it says:- System.Data.DataColumn does not contain a deffination for Convert. Please help — Alexandria, May 05 '15 at 10:47

score -2 · Answer 1 · answered Apr 30 '15 at 15:52

-2

Try converting time from datetime as below.

convert(Time, getdate())

answered Apr 30 '15 at 15:52

Amol Kulkarni

50
3

Where would I put this in? – Alexandria May 05 '15 at 14:30

SqlBulkCopy Time DataType Issue

1 Answers1