Store Time only in a datetime datafield in MS SQL

[calvin]

Hi,

 

I want to store time into a datetime datafield in MS SQL Database, but eventually, it was include a date prefix the time ! I had try the approach on following and get the invalid result.

 

Approach 1:

Dim strTime as String = "11:59:59 AM"

Output:

01-01-1900 11:59:59 AM

 

Approach 2:

Dim dtTime as DateTime = "11:59:59 AM"

Output:

1/1/0001 11:59:59 AM <- (error occur: unable add to DB cause date is not between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM)

 

* The time on the above is accompany with AM/PM.

 

Approach 3:

Dim strTime as String = "11:59:59 AM"

Dim strStartTime2 = DateTime.ParseExact(strStartTime, "hh:mm:ss", System.Globalization.CultureInfo.CurrentCulture)

Output:

01-01-1900 11:59:59 AM

 

Approach 4:

Dim strTime as DateTime = "11:59:59 AM"

Dim strStartTime2 = DateTime.ParseExact(strStartTime, "hh:mm:ss", System.Globalization.CultureInfo.CurrentCulture)

Output:

String was not recognized as a valid DateTime.

 

I know what cause the approach 4 get an error. That is because the strTime is declare as DateTime and that contain "AM/PM" in time.

 

My Question:

1]Can I just insert the time only to the DB without date?

2]If possible, what should I delcare in the variable(String/DateTime/..?)

3]How can I deal with the "AM/PM"? It must concate with time to identify daytime/night

 

If you know and have the solution, pls do me a favor and will be more appreciated.

Thank you

 

Calvin

[Joe Mamma]

In SQL use datetime.

 

in .NET use the DateTime Constructor

 

[C#]

public DateTime(

intyear,

intmonth,

intday,

inthour,

intminute,

intsecond

);[c#]

 

You can enter 0 for year, 1 for month and day, and whatever you need for the time parts.

 

use DateTime.TimeOfDay to get the time after loading the datetime from a SQL query.

 

If you want to pull from the DB in a preformated string, use

 

select convert(varchar(128), myDateField, 108) from myTable

 

but, as a rule, formatting should be done in the presentation layer not in the data access. You wont be able to any calculations using the formatted time.

 

You could also use an INT in the database and store TimeSpan.Ticks

[calvin]

I want to "Store" time into datetime datafield, not "Pull" time from DB. :)

[Derek Stone]

You can't store a lone time value in a datetime field in a Microsoft SQL Server database. Period.

 

As Robby mentioned quite clearly, you'll need to use some other method.

[Joe Mamma]

I want to "Store" time into datetime datafield' date=' not "Pull" time from DB. :)[/quote']

read my response

[Joe Mamma]

string sql = "CREATE TABLE [storing Times For People Who Can't Decipher Posts] ( " +
"[timeAsTicks] [bigint] NULL , " +
"[timeAsDate] [datetime] NULL ," +
"[iD] [uniqueidentifier] NOT NULL DEFAULT (newid()) PRIMARY KEY " +
") ON [PRIMARY] "; 

oleDbConnection1.Open();
oleDbCommand1.Connection = oleDbConnection1;
oleDbCommand1.CommandText = sql;
oleDbCommand1.ExecuteNonQuery();

TimeSpan ts1 = new TimeSpan(0, 13, 5, 23); // 1:05:23 pm;
DateTime dt1 = new DateTime(ts1.Ticks); // 1/1/1900 1:05:23 pm;
sql = "Insert into [storing Times For People Who Can't Decipher Posts] " +
" (timeAsTicks, timeAsDate) values ( ?, ?) ";
oleDbCommand1.CommandText = sql;

oleDbCommand1.Parameters.Add("ticks", System.Data.OleDb.OleDbType.BigInt);
oleDbCommand1.Parameters["ticks"].Value = ts1.Ticks;
oleDbCommand1.Parameters.Add("datetime", System.Data.OleDb.OleDbType.Date);
oleDbCommand1.Parameters["datetime"].Value = dt1;
oleDbCommand1.ExecuteNonQuery();

oleDbCommand1.CommandText = "Select * from [storing Times For People Who Can't Decipher Posts]";

using ( System.Data.OleDb.OleDbDataReader reader = oleDbCommand1.ExecuteReader() )
while ( reader.Read() )
{
 dt1 = new DateTime(reader.GetInt64(0));
 MessageBox.Show("Values for [storing Times For People Who Can't Decipher Posts] " + System.Environment.NewLine +
  "Ticks Time: " + dt1.TimeOfDay.ToString() + System.Environment.NewLine +
  "DateTime Time: " + reader.GetDateTime(1).TimeOfDay.ToString());
}

oleDbCommand1.CommandText = "Drop table [storing Times For People Who Can't Decipher Posts]";
oleDbCommand1.ExecuteNonQuery();