使用SQL命令将日期或时间插入Sql Server [英] Inserting Date or Time into Sql Server Using SQLcommand

查看:115
本文介绍了使用SQL命令将日期或时间插入Sql Server的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在日期时间字段中插入时间值时遇到问题。


我想将此格式的时间值(08:15:39)插入SQL

日期时间字段。

我尝试了很多方法,我可以使用

这个命令以timeonly格式提取值

格式(现在,HH:mm:ss)

但是当我将它插入到Sql Server数据库中时,它用日期值

加载它。 br />
输出看起来像01/01/1900 08:59:00在这种情况下,时间是正确的,但是默认情况下会收到日期,但我甚至不希望有时间和日期一起使用
。如果我想,那么我已经通过了当前日期,所以

应该显示当前日期,但为什么它显示默认的

日期01/01 / 1900年。


我检查过将简单的当前日期插入数据库

并使用Insert命令,但它仍会插入默认日期。

这是我的完整代码。

Dim sqlconn As New SqlConnection(" Data Source = MSC RHD SERVER; user id

= sa; pwd = testserver; initial catalog = testData")

Dim Sqlcom As New SqlCommand


试试


sqlconn.Open()


Dim m_sql As String


m_sql ="插入Logcomms(recdate)"


m_sql = m_sql& "值(&&; DateTime.Today&")"


''MsgBox(m_sql)


Sqlcom.Connection = sqlconn


Sqlcom.CommandText = m_sql


Dim M_result As Integer = Sqlcom.ExecuteNonQuery


Catch ex As Exception


MsgBox(ex.ToString)


最后


sqlconn.Dispose ()


Sqlcom.Dispose()


结束尝试


*请帮帮我吧很紧急。我是VB.NET中的新蜜人

提前致谢


Khurram

解决方案

这就是它是怎么回事。 SQL Server中的DateTime数据类型只是

,即日期和时间。 1/1/1900是使用SQL Server的时间的开始,

所以你可以将它剥离或忽略它。


这可能是不是你想要的,所以这里只有时间返回

的SQL函数。使用方法:选择dbo.udf_TimeOnly(getdate())


更改功能dbo.udf_TimeOnly

(@inDate DATETIME)

返回VARCHAR(11)

AS

BEGIN

DECLARE @outTime AS VARCHAR(11)


SET @outtime = CONVERT(VARCHAR(8),@ indate,8)


如果SUBSTRING(@outTime,1,2)> = 12

BEGIN

IF SUBSTRING(@ outTime,1,2)> 12

SET @ outTime = CAST((SUBSTRING(@outTime,1,2)-12) AS VARCHAR)+

SUBSTRING(@ outTime,3,6)

SET @outTime = @outTime +''PM''

END


ELSE

SET @outTime = @outTime +''AM''


返回@outTime

END

" Khurram" < S ******* @ gmail.com>写在

新闻:或者************** @ TK2MSFTNGP11.phx.gbl:

我有一个问题在datetime字段中插入时间值。

我想在此格式(08:15:39)中仅将时间值插入到
SQL
日期时间字段中。我尝试了很多方法,我可以使用
这个命令以时间格式提取值
格式(现在,HH:mm:ss)
但是当我将它插入到Sql Server数据库中时,它会使用它来加载日期
值。
输出看起来像01/01/1900 08:59:00。在这种情况下,时间
是正确的,但默认情况下会收到日期,但我甚至不想和时间一起约会。如果我想,那么我已经通过了
当前日期,因此应该显示当前日期,但为什么它显示默认日期01/01/1900。

我已经检查过使用Insert命令将简单的当前日期插入到
数据库中,但它仍然会插入默认日期。
这是我的完整代码。
Dim sqlconn As New SqlConnection(" Data Source = MSC RHD SERVER; user
id
= sa; pwd = testserver; initial catalog = testData")

Dim Sqlcom As新的SqlCommand

尝试

sqlconn.Open()

Dim m_sql As String

m_sql ="插入Logcomms(recdate)"

m_sql = m_sql& "值(&&; DateTime.Today&")"

''MsgBox(m_sql)

Sqlcom.Connection = sqlconn
Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

Catch ex As Exception

MsgBox(ex.ToString)

最后

sqlconn.Dispose()

Sqlcom.Dispose()

结束尝试
*请帮帮我,这很紧急。我是VB.NET的新蜜人
在此先感谢

Khurram




非常感谢。

但我的要求是使用当前系统插入时间或日期

使用VB.NET编码的日期和时间。

感谢日志告诉我PL / SQL编码。

但我希望如果你查看我的代码,一切都在VB.net中。

等待更多有利的abswers


" cbDevelopment" <德********* @ remove.700cb.net>在留言中写道

news:Xn *************************** @ 207.46.248.16 ..。

这就是它是怎么回事。 SQL Server中的DateTime数据类型只是
,日期和时间。 1/1/1900是使用SQL Server的时间的开始,
因此您可以将其删除或忽略它。

这可能不是您想要的,所以这里是一个SQL函数只返回时间。使用方法如下:选择dbo.udf_TimeOnly(getdate())

ALTER FUNCTION dbo.udf_TimeOnly
(@ inDate DATETIME)
RETURNS VARCHAR(11)
AS
BEGIN
DECLARE @outTime AS VARCHAR(11)

SET @outtime = CONVERT(VARCHAR(8),@ indate,8)

如果SUBSTRING( @outTime,1,2)> = 12
BEGIN
如果SUBSTRING(@ outTime,1,2)> 12
SET @ outTime = CAST((SUBSTRING(@outTime,1) ,2)-12)AS VARCHAR)+
SUBSTRING(@ outTime,3,6)

SET @outTime = @outTime +''PM''
END

ELSE
SET @outTime = @outTime +''AM''

返回@outTime
END

" Khurram" ; < S ******* @ gmail.com>在
新闻中写道:或者************** @ TK2MSFTNGP11.phx.gbl:

我在插入时间值时遇到问题在datetime字段中。

我想以这种格式(08:15:39)将时间值插入到
SQL
日期时间栏中。
我尝试了很多方法,我可以使用
这个命令以时间格式提取值
格式(现在,HH:mm:ss)
但是当我插入时它进入Sql Server数据库,用它来加载日期
值。
输出看起来像是01/01/1900 08:59:00。在这种情况下,时间
是正确的,但默认情况下会收到日期,但我甚至不想和时间一起约会。如果我想,那么我已经通过了
当前日期,因此应该显示当前日期,但为什么它显示默认日期01/01/1900。

我已经检查过使用Insert命令将简单的当前日期插入到
数据库中,但它仍然会插入默认日期。
这是我的完整代码。
Dim sqlconn As New SqlConnection(" Data Source = MSC RHD SERVER; user
id
= sa; pwd = testserver; initial catalog = testData")

Dim Sqlcom As新的SqlCommand

尝试

sqlconn.Open()

Dim m_sql As String

m_sql ="插入Logcomms(recdate)"

m_sql = m_sql& "值(&&; DateTime.Today&")"

''MsgBox(m_sql)

Sqlcom.Connection = sqlconn
Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

Catch ex As Exception

MsgBox(ex.ToString)

最后

sqlconn.Dispose()

Sqlcom.Dispose()

结束尝试
*请帮帮我,这很紧急。我是VB.NET的新蜜人
提前致谢

Khurram



Khuram,


除了cbDevelompment之外,你不能只在日期时间格式的SQL

服务器中插入一个时间,因为没有时间格式。


您可以将其翻译成很长或其他任何值,但是我不会这样做。一个日期时间01/01/1900意味着疯狂的时间没有约会。


只是为了给别人一个与cbDevelopment相同的答案。


Cor


" Khurram" < s。******* @ gmail.com>

...

非常感谢。
但我的要求是插入使用当前系统的时间或日期
使用VB.NET编码的日期和时间。
感谢日志告诉我PL / SQL编码。
但我希望如果你查看我的代码,所有是在VB.net。
等待更多有利的abswers

cbDevelopment <德********* @ remove.700cb.net>在消息中写道
新闻:Xn *************************** @ 207.46.248.16 ..。

>这是它是怎么回事。 SQL Server中的DateTime数据类型只是
>那个,日期和时间。 1/1/1900是SQL的时间的开始


服务器,

>所以你可以把它剥掉或忽略它。
>
>这可能不是你想要的,所以这里有一个SQL函数


返回

>只有时间。使用方法:选择dbo.udf_TimeOnly(getdate())
>
> ALTER FUNCTION dbo.udf_TimeOnly
> (@inDate DATETIME)
> RETURNS VARCHAR(11)
> AS
>开始
> DECLARE @outTime AS VARCHAR(11)
>
> SET @outtime = CONVERT(VARCHAR(8),@ indate,8)
>
> IF SUBSTRING(@ outTime,1,2)> = 12
>开始
> IF SUBSTRING(@ outTime,1,2)> 12
> SET @ outTime = CAST((SUBSTRING(@outTime,1,2)-12)AS VARCHAR)+
> SUBSTRING(@ outTime,3,6)
>
> SET @outTime = @outTime +''PM''
>结束
>
> ELSE
> SET @outTime = @outTime +''AM''
>
>返回@outTime
>结束
>
>
> " Khurram" < S ******* @ gmail.com>写在
>新闻:或************** @ TK2MSFTNGP11.phx.gbl:
>

>>我在日期时间字段中插入时间值时遇到问题。
>>
>>我想在此格式(08:15:39)中仅将时间值插入
>> SQL
>>日期时间字段。
>>我尝试了很多方法,我可以通过
>>提取timeonly格式的值。使用
>>这个命令
>>格式(现在,HH:mm:ss)
>>但是当我将它插入到Sql Server数据库中时,它包含了日期
>>价值
>>用它。
>>输出看起来像01/01/1900 08:59:00在那种情况下时间
>>是
>>正确,但默认选择日期,但我甚至不想
>>有时间和日期。如果我想,那么我已经通过了
>>当前日期,因此应显示当前日期,但为什么它是
>>显示默认日期01/01/1900。
>>
>>更多我检查将简单的当前日期插入
>>数据库
>>使用插入命令,但仍会插入默认日期。
>>这是我的完整代码。
>> Dim sqlconn As New SqlConnection(Data Source = MSC RHD SERVER; user
>> id
>> = sa; pwd = testserver; initial catalog = testData)
>>
>> Dim Sqlcom As New SqlCommand
>>
>>尝试
>>
>> sqlconn.Open()
>>
>> Dim m_sql As String
>>
>> m_sql ="插入Logcomms(recdate)"
>>
>> m_sql = m_sql& "值(&&; DateTime.Today&")"
>>
>> ''MsgBox(m_sql)
>>
>> Sqlcom.Connection = sqlconn
>>
>> Sqlcom.CommandText = m_sql
>>
>> Dim M_result As Integer = Sqlcom.ExecuteNonQuery
>>
>> C ex ex Exception
>>
>> MsgBox(ex.ToString)
>>
>>最后
>>
>> sqlconn.Dispose()
>>
>> Sqlcom.Dispose()
>>
>>结束尝试
>>
>>
>>
>> *请帮助我,这是紧急的。我是VB.NET的新蜜人
>>在此先感谢
>>
>> Khurram
>>
>>
>>


>



I have a problem while inserting time value in the datetime Field.

I want to Insert only time value in this format (08:15:39) into the SQL
Date time Field.
I tried to many ways, I can extract the value in timeonly format by using
this command
Format(now,"HH:mm:ss")
But when I insert it into the Sql Server database, it embadded date value
with it.
the output looks like that "01/01/1900 08:59:00" in that case time is
correct but date is picked up by default, but I don''t even want to have date
along with time. and If I want then I already passed the Current Date, so
the current date should be displayed, but why it is displaying the default
date "01/01/1900".

More over I checked to insert the simple current date into the database
with the Insert command, but it still inserts the Default date.
Here is my complete code.
Dim sqlconn As New SqlConnection("Data Source =MSC RHD SERVER;user id
=sa;pwd=testserver;initial catalog = testData")

Dim Sqlcom As New SqlCommand

Try

sqlconn.Open()

Dim m_sql As String

m_sql = "Insert into Logcomms (recdate)"

m_sql = m_sql & " values (" & DateTime.Today & ")"

''MsgBox(m_sql)

Sqlcom.Connection = sqlconn

Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

Catch ex As Exception

MsgBox(ex.ToString)

Finally

sqlconn.Dispose()

Sqlcom.Dispose()

End Try

*Please Help me it is Urgent. I am new bee in VB.NET
Thanks in Advance

Khurram

解决方案

This is "just how it is". The DateTime datatype in SQL Server is just
that, date and time. 1/1/1900 is the beginning of time with SQL Server,
so you can just strip it out or ignore it.

That''s probably not what you wanted, so here is a SQL function to return
only the time. Use like: select dbo.udf_TimeOnly(getdate())

ALTER FUNCTION dbo.udf_TimeOnly
(@inDate DATETIME)
RETURNS VARCHAR(11)
AS
BEGIN
DECLARE @outTime AS VARCHAR(11)

SET @outtime=CONVERT(VARCHAR(8),@indate,8)

IF SUBSTRING(@outTime,1,2)>=12
BEGIN
IF SUBSTRING(@outTime,1,2)>12
SET @outTime=CAST((SUBSTRING(@outTime,1,2)-12) AS VARCHAR) +
SUBSTRING(@outTime,3,6)

SET @outTime = @outTime + '' PM''
END

ELSE
SET @outTime = @outTime + '' AM''

RETURN @outTime
END
"Khurram" <s.*******@gmail.com> wrote in
news:OR**************@TK2MSFTNGP11.phx.gbl:

I have a problem while inserting time value in the datetime Field.

I want to Insert only time value in this format (08:15:39) into the
SQL
Date time Field.
I tried to many ways, I can extract the value in timeonly format by
using
this command
Format(now,"HH:mm:ss")
But when I insert it into the Sql Server database, it embadded date
value
with it.
the output looks like that "01/01/1900 08:59:00" in that case time
is
correct but date is picked up by default, but I don''t even want to
have date along with time. and If I want then I already passed the
Current Date, so the current date should be displayed, but why it is
displaying the default date "01/01/1900".

More over I checked to insert the simple current date into the
database
with the Insert command, but it still inserts the Default date.
Here is my complete code.
Dim sqlconn As New SqlConnection("Data Source =MSC RHD SERVER;user
id
=sa;pwd=testserver;initial catalog = testData")

Dim Sqlcom As New SqlCommand

Try

sqlconn.Open()

Dim m_sql As String

m_sql = "Insert into Logcomms (recdate)"

m_sql = m_sql & " values (" & DateTime.Today & ")"

''MsgBox(m_sql)

Sqlcom.Connection = sqlconn

Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

Catch ex As Exception

MsgBox(ex.ToString)

Finally

sqlconn.Dispose()

Sqlcom.Dispose()

End Try

*Please Help me it is Urgent. I am new bee in VB.NET
Thanks in Advance

Khurram




Thanks a lot.
But My Requirement is to insert the Time or date with the Current System
Date and time using VB.NET coding.
Thanks a log for telling me PL/SQL coding.
But I hope if you go through my code, all is in VB.net.
Waiting for any more favourable abswers

"cbDevelopment" <de*********@remove.700cb.net> wrote in message
news:Xn***************************@207.46.248.16.. .

This is "just how it is". The DateTime datatype in SQL Server is just
that, date and time. 1/1/1900 is the beginning of time with SQL Server,
so you can just strip it out or ignore it.

That''s probably not what you wanted, so here is a SQL function to return
only the time. Use like: select dbo.udf_TimeOnly(getdate())

ALTER FUNCTION dbo.udf_TimeOnly
(@inDate DATETIME)
RETURNS VARCHAR(11)
AS
BEGIN
DECLARE @outTime AS VARCHAR(11)

SET @outtime=CONVERT(VARCHAR(8),@indate,8)

IF SUBSTRING(@outTime,1,2)>=12
BEGIN
IF SUBSTRING(@outTime,1,2)>12
SET @outTime=CAST((SUBSTRING(@outTime,1,2)-12) AS VARCHAR) +
SUBSTRING(@outTime,3,6)

SET @outTime = @outTime + '' PM''
END

ELSE
SET @outTime = @outTime + '' AM''

RETURN @outTime
END
"Khurram" <s.*******@gmail.com> wrote in
news:OR**************@TK2MSFTNGP11.phx.gbl:

I have a problem while inserting time value in the datetime Field.

I want to Insert only time value in this format (08:15:39) into the
SQL
Date time Field.
I tried to many ways, I can extract the value in timeonly format by
using
this command
Format(now,"HH:mm:ss")
But when I insert it into the Sql Server database, it embadded date
value
with it.
the output looks like that "01/01/1900 08:59:00" in that case time
is
correct but date is picked up by default, but I don''t even want to
have date along with time. and If I want then I already passed the
Current Date, so the current date should be displayed, but why it is
displaying the default date "01/01/1900".

More over I checked to insert the simple current date into the
database
with the Insert command, but it still inserts the Default date.
Here is my complete code.
Dim sqlconn As New SqlConnection("Data Source =MSC RHD SERVER;user
id
=sa;pwd=testserver;initial catalog = testData")

Dim Sqlcom As New SqlCommand

Try

sqlconn.Open()

Dim m_sql As String

m_sql = "Insert into Logcomms (recdate)"

m_sql = m_sql & " values (" & DateTime.Today & ")"

''MsgBox(m_sql)

Sqlcom.Connection = sqlconn

Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

Catch ex As Exception

MsgBox(ex.ToString)

Finally

sqlconn.Dispose()

Sqlcom.Dispose()

End Try

*Please Help me it is Urgent. I am new bee in VB.NET
Thanks in Advance

Khurram



Khuram,

In addition to cbDevelompment, you can not insert only a time in an SQL
server in datetime format, because there is not a Time format.

You can translate it to a long or whatever other value, however I would not
do it. A DateTime with 01/01/1900 means crazy enough a time withouth a date.

Just to give you from someone else the same answer as cbDevelopment.

Cor

"Khurram" <s.*******@gmail.com>
...

Thanks a lot.
But My Requirement is to insert the Time or date with the Current System
Date and time using VB.NET coding.
Thanks a log for telling me PL/SQL coding.
But I hope if you go through my code, all is in VB.net.
Waiting for any more favourable abswers

"cbDevelopment" <de*********@remove.700cb.net> wrote in message
news:Xn***************************@207.46.248.16.. .

> This is "just how it is". The DateTime datatype in SQL Server is just
> that, date and time. 1/1/1900 is the beginning of time with SQL


Server,

> so you can just strip it out or ignore it.
>
> That''s probably not what you wanted, so here is a SQL function to


return

> only the time. Use like: select dbo.udf_TimeOnly(getdate())
>
> ALTER FUNCTION dbo.udf_TimeOnly
> (@inDate DATETIME)
> RETURNS VARCHAR(11)
> AS
> BEGIN
> DECLARE @outTime AS VARCHAR(11)
>
> SET @outtime=CONVERT(VARCHAR(8),@indate,8)
>
> IF SUBSTRING(@outTime,1,2)>=12
> BEGIN
> IF SUBSTRING(@outTime,1,2)>12
> SET @outTime=CAST((SUBSTRING(@outTime,1,2)-12) AS VARCHAR) +
> SUBSTRING(@outTime,3,6)
>
> SET @outTime = @outTime + '' PM''
> END
>
> ELSE
> SET @outTime = @outTime + '' AM''
>
> RETURN @outTime
> END
>
>
> "Khurram" <s.*******@gmail.com> wrote in
> news:OR**************@TK2MSFTNGP11.phx.gbl:
>

>> I have a problem while inserting time value in the datetime Field.
>>
>> I want to Insert only time value in this format (08:15:39) into the
>> SQL
>> Date time Field.
>> I tried to many ways, I can extract the value in timeonly format by
>> using
>> this command
>> Format(now,"HH:mm:ss")
>> But when I insert it into the Sql Server database, it embadded date
>> value
>> with it.
>> the output looks like that "01/01/1900 08:59:00" in that case time
>> is
>> correct but date is picked up by default, but I don''t even want to
>> have date along with time. and If I want then I already passed the
>> Current Date, so the current date should be displayed, but why it is
>> displaying the default date "01/01/1900".
>>
>> More over I checked to insert the simple current date into the
>> database
>> with the Insert command, but it still inserts the Default date.
>> Here is my complete code.
>> Dim sqlconn As New SqlConnection("Data Source =MSC RHD SERVER;user
>> id
>> =sa;pwd=testserver;initial catalog = testData")
>>
>> Dim Sqlcom As New SqlCommand
>>
>> Try
>>
>> sqlconn.Open()
>>
>> Dim m_sql As String
>>
>> m_sql = "Insert into Logcomms (recdate)"
>>
>> m_sql = m_sql & " values (" & DateTime.Today & ")"
>>
>> ''MsgBox(m_sql)
>>
>> Sqlcom.Connection = sqlconn
>>
>> Sqlcom.CommandText = m_sql
>>
>> Dim M_result As Integer = Sqlcom.ExecuteNonQuery
>>
>> Catch ex As Exception
>>
>> MsgBox(ex.ToString)
>>
>> Finally
>>
>> sqlconn.Dispose()
>>
>> Sqlcom.Dispose()
>>
>> End Try
>>
>>
>>
>> *Please Help me it is Urgent. I am new bee in VB.NET
>> Thanks in Advance
>>
>> Khurram
>>
>>
>>


>




这篇关于使用SQL命令将日期或时间插入Sql Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆