Format string into Time from datatable C#

Question

I am creating a datatable from excel import and bulkcopying it to database, I need to put the Scheduled Start as start time into database as time hh:mm:ss but it keeps going into database as 30/12/1899 10:30:00, all I want is the 10:30:00 to go into database. How can I do this?

I have tried converting to DateTime and then formating but keeps saying it is not a valid DateTime.

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

            }
            excel_con.Close();


            if (dtExcelData.Rows.Count > 0)
            {
                foreach (DataRow rw in dtExcelData.Rows)
                {
                    //Creates StaffID
                     rw["StaffID"] = "00" + rw["Host Key of Staff"].ToString();

                    //Creates Start Time
                     rw["Scheduled Start as start time"] = rw["Scheduled Start as start time"].ToString("hh-mm");

                }
               // Response.Write(strConcate);
            }

Use the right datatype, so datetime instead of string. – Tim Schmelter May 06 '15 at 15:00 — Tim Schmelter, May 06 '15 at 15:00

score 0 · Answer 1 · answered May 06 '15 at 15:08

0

but keeps saying it is not a valid DateTime

i think your field in db is DateTime type.this is for what you are doing in your foreach.your are converting it to a 'string' and now you try to save a string .change the db field from DateTime to nchar or nvarchar and store time in string format the way you want. now every time you get data from db

answered May 06 '15 at 15:08

sbsr

352
3
9

I have tried Field in database as dateTime and nvarchar doesnt make a difference. – Redheadinferno May 07 '15 at 08:31

Grant Winney · Accepted Answer · 2015-05-06T15:15:28.887

0

You can't have a DateTime without both a date and a time. If you try to omit the time, it'll probably default to midnight. If you omit the date, it apparently defaults to Dec 30, 1899 in the database.

If you don't see a column type for only Time, you'll have to either:

use a string or text type for that column and store the time as a string like you're currently attempting to do, or
keep the column as a Date/Time and don't worry about the date portion that's stored

I'd do it the second way, and then use a format string like .ToString("hh-mm") so that the user only sees the portion you want them to see. They'll never realize you've stored a date. This way, you're not storing the time as a string and then needing to manipulate it back into the correct format later on when you inevitably try to do some calculation with it.

edited May 06 '15 at 15:15

answered May 06 '15 at 15:08

Grant Winney

65,241
13
115
165

So I set database column type to DateTime and change string to DateTime as well? – Redheadinferno May 07 '15 at 08:30
Ok So I have put the date in as DateTime and it is storing as `1899-12-30 01:00:00.000` I only want to show the user in a gridview 01:00 how do I do this? Do I create sql statement and select the data from database but what do I do to get each result and format the Scheduled Start as Start time. Sorry for being a pain – Redheadinferno May 07 '15 at 10:44
Nope not using WinForms :s should i? – Redheadinferno May 07 '15 at 10:57
I am using asp.net with c# and a normal gridview. – Redheadinferno May 07 '15 at 11:00
Do you have any idea how I would get just the hh:mm from the Scheduled Start as Start time in c# and store as integer? – Redheadinferno May 07 '15 at 11:50
I have another column called TotalHoursTaught which has the data 1899-12-30 01:30:00 stored, when I show it to users it will be displayed as 01:30 which means 1 hour 30 minutes which is find but I want to change this is 1.50 which means 1 and hour hours, I know I have to * 01:30 by 24 so get this but dont have a clue how to do this in code, at moment I have doing it just in excel document. – Redheadinferno May 07 '15 at 11:59

score 0 · Answer 3 · answered May 06 '15 at 15:10

0

I believe your problem is with this line

rw["Scheduled Start as start time"] = rw["Scheduled Start as start time"].ToString("hh-mm");

If rw["Scheduled Start as start time"] is a DateTime as I suspect, then you cannot then assign a string to it...its a DateTime, not a string. It must have a full date and time.

You could change the type to be a string and store the datetime elsewhere or store the string elsewhere.

answered May 06 '15 at 15:10

Notts90

254
2
20

I dont know what you mean, whatever I store it has DateTime keeps saying is not valid DateTime. Dont know what im doing wrong. – Redheadinferno May 07 '15 at 08:29

score -1 · Answer 4 · answered Aug 18 '15 at 05:16

-1

try this one

String.Format("{0:t}", dt); // "4:05 PM" ShortTime String.Format("{0:d}", dt); // "3/9/2008" ShortDate

answered Aug 18 '15 at 05:16

Rafiq J

1
1

Format string into Time from datatable C#

4 Answers4