Inserting into a Time column

[fniles]

I am using VB.NET 2003 and SQL Server 2000.
I have a table with a datetime column type. When inserting into the table
for that column, I set it to Date.Now.ToString("T")
, which is something like "2:50:54 PM". But after the row is inserted and I
check the data in the database, the column data is set to "1/7/2007 2:50:04
PM" (notice today's date in front of the time). If I insert data directly
into the table in the Enterprise Manager, the time stays to be "2:50:54 PM",
but in Query Analyzer if I run the stored procedure like INSERT_INTO_MYTABLE
'2:54:50 PM', the time is set to "1/1/1900 2:54:50 PM".
Is there any way to insert only the time (not including the date) into a
datetime column ?
Thank you.

Dim cmdSQL As SqlClient.SqlCommand

With cmdSQL
.Connection = adoCon
.CommandType = CommandType.StoredProcedure
.CommandText = "INSERT_INTO_MYTABLE"
.Parameters.Add("@Time", SqlDbType.DateTime, 8).Value =
Date.Now.ToString("T")
.ExecuteNonQuery()
End with

CREATE PROCEDURE INSERT_INTO_MYTABLE
@Time datetime = NULL
AS
insert into MYTABLE (Time) VALUES (@Time)

 

[David Portas]

I am using VB.NET 2003 and SQL Server 2000.
I have a table with a datetime column type. When inserting into the table
for that column, I set it to Date.Now.ToString("T")
, which is something like "2:50:54 PM". But after the row is inserted and I
check the data in the database, the column data is set to "1/7/2007 2:50:04
PM" (notice today's date in front of the time). If I insert data directly
into the table in the Enterprise Manager, the time stays to be "2:50:54 PM",
but in Query Analyzer if I run the stored procedure like INSERT_INTO_MYTABLE
'2:54:50 PM', the time is set to "1/1/1900 2:54:50 PM".
Is there any way to insert only the time (not including the date) into a
datetime column ?

No. DATETIME always includes both date and time components.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Hari Prasad]

Hello,

In a DATETIME data type you can not have only the Date and time. If you need
only the Time then probably you could declare as VARCHAR datatype.

Thanks
Hari

 

[Erland Sommarskog]

I am using VB.NET 2003 and SQL Server 2000. I have a table with a
datetime column type. When inserting into the table for that column, I
set it to Date.Now.ToString("T") , which is something like "2:50:54 PM".
But after the row is inserted and I check the data in the database, the
column data is set to "1/7/2007 2:50:04 PM" (notice today's date in
front of the time). If I insert data directly into the table in the
Enterprise Manager, the time stays to be "2:50:54 PM", but in Query
Analyzer if I run the stored procedure like INSERT_INTO_MYTABLE '2:54:50
PM', the time is set to "1/1/1900 2:54:50 PM".
Is there any way to insert only the time (not including the date) into a
datetime column ?

No, obviously else it would not be called datetime. When you present a
string to a datetime column, defaults are applied for all missing fields,
and for date that is 1900-01-01. Then it's up to the client to interpret
that date. Does it mean "This took place on the first day of the 20th
century?" Or does it mean "Date is not important"?



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Plamen Ratchev]

As everybody else noted there is no way to cut the date portion of a
datetime field. But do you really have to?

I have a scheduling application where date does not matter and only time is
important (it creates a template of a weekly schedule that then can be
applied to current calendar week). It was no problem to leave the default
date '1/1/1900' with the time. Truncating the date portion in the client
application is very easy. But having the field as datetime has big
advantages as it lets you use the native SQL Server functions to perform
calculations on the time portion, as well as ordering.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

 

[fniles]

Thank you all for the replies.

When you present a string to a datetime column, defaults are applied for
all missing fields,
and for date that is 1900-01-01.

Why when I insert a new row from VB.NET program, the column is set to
today's date (ie 1/7/2007 2:50:04
PM --> I did it yesterday), instead of 01/01/1900 ?

 

[Plamen Ratchev]

When you do the insert directly in SQL Server it takes the default for the
date field in SQL Server which is '1/1/1900'. When you do the insert from
your VB.NET code it will use the current date from the client side (the
conversion for the date field takes place on the client side).

Regards,

Plamen Ratchev
http://www.SQLStudio.com