Reading DateTime from database

[Mitja Bonca]

I inserted a value of DateTime into database, like "26.12.2009 11:50:00" .
Is it possible that the reader will read only the time of a day from this, without date?

So far I did only that it reads everything, date and time:

string myTime = Convert.ToDateTime(dataGridView1.Rows[IndexVrstica].Cells[0].Value).ToShortTimeString();
DateTime myTime2 = Convert.ToDateTime(myTime);

it has to be something like:

DateTime myTime2 = Convert.ToDateTime(myTime).ToShortTimeString();

but this is not ok, beucase I got an error, that it can not be transformed from date to string.

As I said, I would like that it reads only time. Is this possible?
If not, I will have to change some other thing, which I do not know how.

[ddanbe]

I tried this and it worked:

string myTime = "26.12.2009 11:50:00";
            DateTime dt = Convert.ToDateTime(myTime);
            string myHour = dt.ToShortTimeString();

[Ionelul]

Hi Matja,

First of all, be sure that the date's format from string is the same with your system's datetime format.

The following line gets the time value only.

TimeSpan myTime = Convert.ToDateTime(dataGridView1.Rows[IndexVrstica].Cells[0].Value).TimeOfDay;

Cheers,
Ionut

[sknake]

>>I inserted a value of DateTime into database, like "26.12.2009 11:50:00" .
Is it possible that the reader will read only the time of a day from this, without date?

What type of database are you working with? You should use parameterized SQL when inserting data. If you convert the date to a string then insert the date it will break if your application is run on computer with other culture settings. Some cultures use m/d/y, some use d/m/y, etc. I don't think the Time datatype exists in most databases. Typically if you want to store only the timer portion of a date you would use an integer.

Inserting a native DateTime value:

private void button3_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string query = "Insert Into UserTable (UserName, DateHired) Values (@UserName, @DateHired)";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar)).Value = "sknake";
          cmd.Parameters.Add(new SqlParameter("@DateHired", SqlDbType.DateTime)).Value = DateTime.Today;
          cmd.ExecuteNonQuery();
        }
        conn.Close();
      }
    }

Now regarding the other method it depends on how accurate you need the time value. For most applications I would think that hours, minutes and seconds would be the only portion of the date you would care about. You could use an int32 to store the time portion like I mentioned above:

private void button4_Click(object sender, EventArgs e)
    {
      int time = Convert.ToInt32(Math.Truncate(DateTime.Now.TimeOfDay.TotalSeconds));
      MessageBox.Show(string.Format("Time: {0:F0}", time));
    }

Now to get that back to a DateTime you have a lot of options but the "best" way depends on what you're doing with it. One example would be:

private void button5_Click(object sender, EventArgs e)
    {
      DateTime dtStart = DateTime.Now;
      int iTime = Convert.ToInt32(Math.Truncate(dtStart.TimeOfDay.TotalSeconds));
      DateTime dtParsedTime = DateTime.Today.AddSeconds(iTime);
      MessageBox.Show(string.Format(
        "Start time: {0:G}" + Environment.NewLine +
        "Integer value: {1:F0}" + Environment.NewLine +
        "Parsed Value: {2:T}" + Environment.NewLine,
        dtStart, //0
        iTime, //1
        dtParsedTime)); //2
    }

Which results in:

Start time: 12/26/2009 11:16:03 AM
Integer value: 40563
Parsed Value: 11:16:03 AM

In that case the time portion of today is stored in iTime . To get the Time portion of that value in to a DateTime struct I took DateTime.Today and added the seconds to the resulting date, and only showed the time portion. The base date matters because if you show this data in grid with different base dates and sort the column it won't sort properly.

The other half of the this approach is how the data is physically stored in the database. DateTime is not an integral value, it has a decimal portion. The integer portion of the value represents the number of days that have passed, so it is the "date" portion if you will. The decimal portion is the "time" part. Take this query/results for example:

Select GetDate() as [date], Cast(GetDate() as float) as [date2]

Results:

date                    date2
----------------------- ----------------------
2009-12-26 11:14:56.920 40171.4687143519
2009-12-26 11:15:02.920 40171.4687837963

If you only care about reading the time portion and you have different base-dates you will lose the performance advantages of indexes. Database indexes will be for the entire value (integral & decimal) but if you only care about the latter half, you will be inefficiently using the database.

So with all of that said I would consider converting the "Time" portion to an integer and storing that value. You will have to convert it back to a DateTime when you get the results back from the SQL Select query, or you could modify the query to handle it for you:
(Using the above snippet for generating the time portion)

private void button4_Click(object sender, EventArgs e)
    {
      int time = Convert.ToInt32(Math.Truncate(DateTime.Now.TimeOfDay.TotalSeconds));
      Console.WriteLine((string.Format("Time: {0:F0}", time)));
    }

Generates these time values:

Time: 40986
Time: 40987
Time: 40988
Time: 40989

Insert and select them back:

SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
Create Table #Table
(
  RecordId int identity(1000, 1) PRIMARY KEY,
  [Time] int NOT NULL
)

Insert Into #Table ([Time]) Values (40986)
Insert Into #Table ([Time]) Values (40987)
Insert Into #Table ([Time]) Values (40988)
Insert Into #Table ([Time]) Values (40989)
SET NOCOUNT OFF
--Ok we have the test-environment set up now
GO 

--Generate our base-date for the transaction
Declare @dt DateTime
Set @dt = Cast(Floor(Cast(GetDate() as float)) as DateTime) 

Select *, DateAdd(second, #Table.Time, @dt) As ResultingDate
From #Table

Results in:

RecordId    Time        ResultingDate
----------- ----------- -----------------------
1000        40986       2009-12-26 11:23:06.000
1001        40987       2009-12-26 11:23:07.000
1002        40988       2009-12-26 11:23:08.000
1003        40989       2009-12-26 11:23:09.000

(4 row(s) affected)

[eeyc]

you always can convert string to Datetime.

Datetime dt = DateTime.pars(SQLreader[Datetime].ToString());

and wחen you have Datetime you can do with it what ever you want.
and take any part of time or date.