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.ToStri ng("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_MYT ABLE
'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.SqlCo mmand

With cmdSQL
.Connection = adoCon
.CommandType = CommandType.Sto redProcedure
.CommandText = "INSERT_INTO_MY TABLE"
.Parameters.Add ("@Time", SqlDbType.DateT ime, 8).Value =
Date.Now.ToStri ng("T")
.ExecuteNonQuer y()
End with

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

[David Portas]

Re: Inserting into a Time column

fniles wrote:

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.ToStri ng("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_MYT ABLE
'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/m...S,SQL.90).aspx
--

[Hari Prasad]

Re: Inserting into a Time column

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

"fniles" <fniles@pfmail. comwrote in message
news:e60a%238pM HHA.3872@TK2MSF TNGP06.phx.gbl. ..

>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.ToStri ng("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_MYT ABLE '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.SqlCo mmand
>
With cmdSQL
.Connection = adoCon
.CommandType = CommandType.Sto redProcedure
.CommandText = "INSERT_INTO_MY TABLE"
.Parameters.Add ("@Time", SqlDbType.DateT ime, 8).Value =
Date.Now.ToStri ng("T")
.ExecuteNonQuer y()
End with
>
CREATE PROCEDURE INSERT_INTO_MYT ABLE
@Time datetime = NULL
AS
insert into MYTABLE (Time) VALUES (@Time)
>
>

[Erland Sommarskog]

Re: Inserting into a Time column

fniles (fniles@pfmail. com) writes:

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.ToStri ng("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_MYT ABLE '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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Plamen Ratchev]

Re: Inserting into a Time column

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" <fniles@pfmail. comwrote in message
news:e60a%238pM HHA.3872@TK2MSF TNGP06.phx.gbl. ..

>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.ToStri ng("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_MYT ABLE '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.SqlCo mmand
>
With cmdSQL
.Connection = adoCon
.CommandType = CommandType.Sto redProcedure
.CommandText = "INSERT_INTO_MY TABLE"
.Parameters.Add ("@Time", SqlDbType.DateT ime, 8).Value =
Date.Now.ToStri ng("T")
.ExecuteNonQuer y()
End with
>
CREATE PROCEDURE INSERT_INTO_MYT ABLE
@Time datetime = NULL
AS
insert into MYTABLE (Time) VALUES (@Time)
>
>

[fniles]

Re: Inserting into a Time column

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" <Plamen@SQLStud io.comwrote in message
news:uNdZyVtMHH A.4888@TK2MSFTN GP02.phx.gbl...

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" <fniles@pfmail. comwrote in message
news:e60a%238pM HHA.3872@TK2MSF TNGP06.phx.gbl. ..

>>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.ToStri ng("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_MY TABLE '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.SqlCo mmand
>>
>With cmdSQL
>.Connection = adoCon
>.CommandType = CommandType.Sto redProcedure
>.CommandText = "INSERT_INTO_MY TABLE"
>.Parameters.Ad d("@Time", SqlDbType.DateT ime, 8).Value =
>Date.Now.ToStr ing("T")
>.ExecuteNonQue ry()
>End with
>>
>CREATE PROCEDURE INSERT_INTO_MYT ABLE
>@Time datetime = NULL
>AS
>insert into MYTABLE (Time) VALUES (@Time)
>>
>>

>
>

[Plamen Ratchev]

Re: Inserting into a Time column

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

"fniles" <fniles@pfmail. comwrote in message
news:%23i7aJPzM HHA.4916@TK2MSF TNGP06.phx.gbl. ..

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" <Plamen@SQLStud io.comwrote in message
news:uNdZyVtMHH A.4888@TK2MSFTN GP02.phx.gbl...

>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" <fniles@pfmail. comwrote in message
>news:e60a%238p MHHA.3872@TK2MS FTNGP06.phx.gbl ...

>>>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.ToStri ng("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_MYT ABLE '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.SqlCo mmand
>>>
>>With cmdSQL
>>.Connection = adoCon
>>.CommandTyp e = CommandType.Sto redProcedure
>>.CommandTex t = "INSERT_INTO_MY TABLE"
>>.Parameters.A dd("@Time", SqlDbType.DateT ime, 8).Value =
>>Date.Now.ToSt ring("T")
>>.ExecuteNonQu ery()
>>End with
>>>
>>CREATE PROCEDURE INSERT_INTO_MYT ABLE
>>@Time datetime = NULL
>>AS
>>insert into MYTABLE (Time) VALUES (@Time)
>>>
>>>

>>
>>

>
>