473,245 Members | 1,374 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,245 software developers and data experts.

Inserting into a Time column

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)
Jan 7 '07 #1
6 3278
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.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/m...S,SQL.90).aspx
--

Jan 7 '07 #2
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" <fn****@pfmail.comwrote in message
news:e6****************@TK2MSFTNGP06.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.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)


Jan 7 '07 #3
fniles (fn****@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.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, es****@sommarskog.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
Jan 7 '07 #4
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" <fn****@pfmail.comwrote in message
news:e6****************@TK2MSFTNGP06.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.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)


Jan 8 '07 #5
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" <Pl****@SQLStudio.comwrote in message
news:uN**************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:e6****************@TK2MSFTNGP06.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.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)



Jan 8 '07 #6
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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP06.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" <Pl****@SQLStudio.comwrote in message
news:uN**************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:e6****************@TK2MSFTNGP06.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.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)




Jan 8 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: R. Santiago | last post by:
I have a table cc_rd_user_questions with the following columns: NUM NUMBER (25) DEFAULT 1 NOT NULL, COMPANY_ID NUMBER (15) NOT NULL, PROJ_ID NUMBER (15) NOT NULL, SEQ ...
2
by: FizzBin | last post by:
We are writing a C application that is using ODBC to insert records into a database. We have a NOT NULL column that can legitimately have an empty value, i.e. we know the value and it is empty...
3
by: Tyler Hudson | last post by:
/*Code below raises following errors: Server: Msg 245, Level 16, State 1, Line 6 Syntax error converting the varchar value 'a' to a column of data type int. */ create table #x (i integer, c...
3
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when...
2
by: a | last post by:
NEW Post Here's my best guess at how to insert this dataset.... the code runs, but no new records are added to the sql table. I've read and split a delimited text file into a dataset. It...
0
by: hafeez | last post by:
Hi , My requirement is to read and write the data from remote SQL SERVER. Locally i have SQL SERVER and i want read and write the data from remote SQL SERVER. I am able to read the data from...
4
by: Manikandan | last post by:
Hi, I'm inserting a datetime values into sql server 2000 from c# SQL server table details Table name:date_test columnname datatype No int date_t DateTime ...
4
priyan
by: priyan | last post by:
hai everyone, I am having a doubt in inserting data in time field. I am having a table in which in column in timestamp without time zone datatype. I want to insert a row into the table but...
5
by: dos360 | last post by:
Hello, I have two tables, one is a list of activities, the other a list of participants. I want to insert one record in the activities table and then using its identity column as foreign key, I...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.