Inserting Date or Time into Sql Server Using SQLcommand

[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=testser ver;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.Connecti on = sqlconn

Sqlcom.CommandT ext = m_sql

Dim M_result As Integer = Sqlcom.ExecuteN onQuery

Catch ex As Exception

MsgBox(ex.ToStr ing)

Finally

sqlconn.Dispose ()

Sqlcom.Dispose( )

End Try



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

Khurram

[cbDevelopment]

Re: Inserting Date or Time into Sql Server Using SQLcommand

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_TimeOnl y(getdate())

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

SET @outtime=CONVER T(VARCHAR(8),@i ndate,8)

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

SET @outTime = @outTime + ' PM'
END

ELSE
SET @outTime = @outTime + ' AM'

RETURN @outTime
END


"Khurram" <s.khurram@gmai l.com> wrote in
news:ORSxJ5wsEH A.2536@TK2MSFTN GP11.phx.gbl:
[color=blue]
> 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=testser ver;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.Connecti on = sqlconn
>
> Sqlcom.CommandT ext = m_sql
>
> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
>
> Catch ex As Exception
>
> MsgBox(ex.ToStr ing)
>
> Finally
>
> sqlconn.Dispose ()
>
> Sqlcom.Dispose( )
>
> End Try
>
>
>
> *Please Help me it is Urgent. I am new bee in VB.NET
> Thanks in Advance
>
> Khurram
>
>
>[/color]

[Khurram]

Re: Inserting Date or Time into Sql Server Using SQLcommand

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

"cbDevelopm ent" <development@re move.700cb.net> wrote in message
news:Xns9583CCA B13FF3cbDevelop ment@207.46.248 .16...[color=blue]
> 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_TimeOnl y(getdate())
>
> ALTER FUNCTION dbo.udf_TimeOnl y
> (@inDate DATETIME)
> RETURNS VARCHAR(11)
> AS
> BEGIN
> DECLARE @outTime AS VARCHAR(11)
>
> SET @outtime=CONVER T(VARCHAR(8),@i ndate,8)
>
> IF SUBSTRING(@outT ime,1,2)>=12
> BEGIN
> IF SUBSTRING(@outT ime,1,2)>12
> SET @outTime=CAST(( SUBSTRING(@outT ime,1,2)-12) AS VARCHAR) +
> SUBSTRING(@outT ime,3,6)
>
> SET @outTime = @outTime + ' PM'
> END
>
> ELSE
> SET @outTime = @outTime + ' AM'
>
> RETURN @outTime
> END
>
>
> "Khurram" <s.khurram@gmai l.com> wrote in
> news:ORSxJ5wsEH A.2536@TK2MSFTN GP11.phx.gbl:
>[color=green]
>> 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=testser ver;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.Connecti on = sqlconn
>>
>> Sqlcom.CommandT ext = m_sql
>>
>> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
>>
>> Catch ex As Exception
>>
>> MsgBox(ex.ToStr ing)
>>
>> Finally
>>
>> sqlconn.Dispose ()
>>
>> Sqlcom.Dispose( )
>>
>> End Try
>>
>>
>>
>> *Please Help me it is Urgent. I am new bee in VB.NET
>> Thanks in Advance
>>
>> Khurram
>>
>>
>>[/color]
>[/color]

[Cor Ligthert]

Re: Inserting Date or Time into Sql Server Using SQLcommand

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.khurram@gmai l.com>
...[color=blue]
> 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
>
> "cbDevelopm ent" <development@re move.700cb.net> wrote in message
> news:Xns9583CCA B13FF3cbDevelop ment@207.46.248 .16...[color=green]
> > 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[/color]
> Server,[color=green]
> > so you can just strip it out or ignore it.
> >
> > That's probably not what you wanted, so here is a SQL function to[/color]
> return[color=green]
> > only the time. Use like: select dbo.udf_TimeOnl y(getdate())
> >
> > ALTER FUNCTION dbo.udf_TimeOnl y
> > (@inDate DATETIME)
> > RETURNS VARCHAR(11)
> > AS
> > BEGIN
> > DECLARE @outTime AS VARCHAR(11)
> >
> > SET @outtime=CONVER T(VARCHAR(8),@i ndate,8)
> >
> > IF SUBSTRING(@outT ime,1,2)>=12
> > BEGIN
> > IF SUBSTRING(@outT ime,1,2)>12
> > SET @outTime=CAST(( SUBSTRING(@outT ime,1,2)-12) AS VARCHAR) +
> > SUBSTRING(@outT ime,3,6)
> >
> > SET @outTime = @outTime + ' PM'
> > END
> >
> > ELSE
> > SET @outTime = @outTime + ' AM'
> >
> > RETURN @outTime
> > END
> >
> >
> > "Khurram" <s.khurram@gmai l.com> wrote in
> > news:ORSxJ5wsEH A.2536@TK2MSFTN GP11.phx.gbl:
> >[color=darkred]
> >> 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=testser ver;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.Connecti on = sqlconn
> >>
> >> Sqlcom.CommandT ext = m_sql
> >>
> >> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
> >>
> >> Catch ex As Exception
> >>
> >> MsgBox(ex.ToStr ing)
> >>
> >> Finally
> >>
> >> sqlconn.Dispose ()
> >>
> >> Sqlcom.Dispose( )
> >>
> >> End Try
> >>
> >>
> >>
> >> *Please Help me it is Urgent. I am new bee in VB.NET
> >> Thanks in Advance
> >>
> >> Khurram
> >>
> >>
> >>[/color]
> >[/color]
>
>[/color]

[David Fúnez]

Re: Inserting Date or Time into Sql Server Using SQLcommand

If you just need the Time why are you using a DateTime Field...?
instead use a Char field, take the part of the Time you want save an put it
in a Char field.

Have a nice day.

--
David Fúnez
Tegucigalpa, Honduras

"La Piratería Mata Las Ideas"
Desarrollador Cinco Estrellas Visual FoxPro


"Khurram" <s.khurram@gmai l.com> wrote in message
news:ORSxJ5wsEH A.2536@TK2MSFTN GP11.phx.gbl...[color=blue]
> 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[/color]
using[color=blue]
> this command
> Format(now,"HH: mm:ss")
> But when I insert it into the Sql Server database, it embadded date[/color]
value[color=blue]
> 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[/color]
date[color=blue]
> 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=testser ver;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.Connecti on = sqlconn
>
> Sqlcom.CommandT ext = m_sql
>
> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
>
> Catch ex As Exception
>
> MsgBox(ex.ToStr ing)
>
> Finally
>
> sqlconn.Dispose ()
>
> Sqlcom.Dispose( )
>
> End Try
>
>
>
> *Please Help me it is Urgent. I am new bee in VB.NET
> Thanks in Advance
>
> Khurram
>
>[/color]

[cbDevelopment]

Re: Inserting Date or Time into Sql Server Using SQLcommand

OK, let's try something different. You need to store a time value, but
you need the date to always be current? Is that on insert or retrieval?
Regardless, it sounds like you want to do this all in VB.

Here is some code that will always use the current date and will append
whatever time you specify or retrieve to it. Be sure to wrap any
date.parse's in try blocks because you may be working with invalid data
if it's user-submitted.

Hope that helps.

' Insert code
Dim sTime As String
Dim dInDate As Date
Dim dOutDate As Date
Dim dFromDB As Date

sTime = "4:30:00 pm"
dInDate = Date.Parse(Now. Date.ToShortDat eString & " " & sTime)

' Extract code
dFromDB = #10/31/2004 6:30:00 PM#
dOutDate = Date.Parse(Now. Date.ToShortDat eString & " " & Format(dFromDB,
"HH:mm:ss") )


"Khurram" <s.khurram@gmai l.com> wrote in
news:OqiNwFysEH A.3884@TK2MSFTN GP10.phx.gbl:
[color=blue]
> 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
>
> "cbDevelopm ent" <development@re move.700cb.net> wrote in message
> news:Xns9583CCA B13FF3cbDevelop ment@207.46.248 .16...[color=green]
> > 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_TimeOnl y(getdate())
> >
> > ALTER FUNCTION dbo.udf_TimeOnl y
> > (@inDate DATETIME)
> > RETURNS VARCHAR(11)
> > AS
> > BEGIN
> > DECLARE @outTime AS VARCHAR(11)
> >
> > SET @outtime=CONVER T(VARCHAR(8),@i ndate,8)
> >
> > IF SUBSTRING(@outT ime,1,2)>=12
> > BEGIN
> > IF SUBSTRING(@outT ime,1,2)>12
> > SET @outTime=CAST(( SUBSTRING(@outT ime,1,2)-12) AS VARCHAR) +
> > SUBSTRING(@outT ime,3,6)
> >
> > SET @outTime = @outTime + ' PM'
> > END
> >
> > ELSE
> > SET @outTime = @outTime + ' AM'
> >
> > RETURN @outTime
> > END
> >
> >
> > "Khurram" <s.khurram@gmai l.com> wrote in
> > news:ORSxJ5wsEH A.2536@TK2MSFTN GP11.phx.gbl:
> >[color=darkred]
> >> 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=testser ver;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.Connecti on = sqlconn
> >>
> >> Sqlcom.CommandT ext = m_sql
> >>
> >> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
> >>
> >> Catch ex As Exception
> >>
> >> MsgBox(ex.ToStr ing)
> >>
> >> Finally
> >>
> >> sqlconn.Dispose ()
> >>
> >> Sqlcom.Dispose( )
> >>
> >> End Try
> >>
> >>
> >>
> >> *Please Help me it is Urgent. I am new bee in VB.NET
> >> Thanks in Advance
> >>
> >> Khurram
> >>
> >>
> >>[/color]
> >[/color]
>
>[/color]

[Cor Ligthert]

Re: Inserting Date or Time into Sql Server Using SQLcommand

cb Develompment,

Your solution works only in a a part of the world.
Some places the US and Anglo Canadia and surely not the major part of the
world.

Cor

[cbDevelopment]

Re: Inserting Date or Time into Sql Server Using SQLcommand

Agreed. Any time you are introducing globalization, there are many design
factors to consider. I did not interpret the original question to require
such consideration.

"Cor Ligthert" <notmyfirstname @planet.nl> wrote in
news:u66Lyv6sEH A.224@TK2MSFTNG P15.phx.gbl:
[color=blue]
> cb Develompment,
>
> Your solution works only in a a part of the world.
> Some places the US and Anglo Canadia and surely not the major part of
> the world.
>
> Cor
>
>
>[/color]

[Khurram]

Re: Inserting Date or Time into Sql Server Using SQLcommand


Hi,
I tried this code snipset, but it gives me that error, that date is out of
range.
In all cases it gives me the same error, of out of range, I applied three
differant ways to insert the date and time intot it, but no Luck

Please give any more suggestions??

Thanks in advance
Dim sTime As String

Dim dInDate As Date

Dim dOutDate As Date

Dim dFromDB As Date

sTime = "4:30:00 pm"

dInDate = Date.Parse(Now. Date.ToShortDat eString & " " & sTime)

Dim m_sql As String

m_sql = "Insert into Logcomms (recdate)"

m_sql = m_sql & " values ('" & CDate(dInDate) & "')" 'First Method

''''m_sql = m_sql & " values (" & dInDate & ")" 'Second Method

''''m_sql = m_sql & " values ('" & dInDate & "')" 'Third Method

Sqlcom.Connecti on = sqlconn

Sqlcom.CommandT ext = m_sql

Dim M_result As Integer = Sqlcom.ExecuteN onQuery



"cbDevelopm ent" <development@re move.700cb.net> wrote in message
news:Xns95848F1 704A89cbDevelop ment@207.46.248 .16...[color=blue]
> OK, let's try something different. You need to store a time value, but
> you need the date to always be current? Is that on insert or retrieval?
> Regardless, it sounds like you want to do this all in VB.
>
> Here is some code that will always use the current date and will append
> whatever time you specify or retrieve to it. Be sure to wrap any
> date.parse's in try blocks because you may be working with invalid data
> if it's user-submitted.
>
> Hope that helps.
>
> ' Insert code
> Dim sTime As String
> Dim dInDate As Date
> Dim dOutDate As Date
> Dim dFromDB As Date
>
> sTime = "4:30:00 pm"
> dInDate = Date.Parse(Now. Date.ToShortDat eString & " " & sTime)
>
> ' Extract code
> dFromDB = #10/31/2004 6:30:00 PM#
> dOutDate = Date.Parse(Now. Date.ToShortDat eString & " " & Format(dFromDB,
> "HH:mm:ss") )
>
>
> "Khurram" <s.khurram@gmai l.com> wrote in
> news:OqiNwFysEH A.3884@TK2MSFTN GP10.phx.gbl:
>[color=green]
>> 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
>>
>> "cbDevelopm ent" <development@re move.700cb.net> wrote in message
>> news:Xns9583CCA B13FF3cbDevelop ment@207.46.248 .16...[color=darkred]
>> > 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_TimeOnl y(getdate())
>> >
>> > ALTER FUNCTION dbo.udf_TimeOnl y
>> > (@inDate DATETIME)
>> > RETURNS VARCHAR(11)
>> > AS
>> > BEGIN
>> > DECLARE @outTime AS VARCHAR(11)
>> >
>> > SET @outtime=CONVER T(VARCHAR(8),@i ndate,8)
>> >
>> > IF SUBSTRING(@outT ime,1,2)>=12
>> > BEGIN
>> > IF SUBSTRING(@outT ime,1,2)>12
>> > SET @outTime=CAST(( SUBSTRING(@outT ime,1,2)-12) AS VARCHAR) +
>> > SUBSTRING(@outT ime,3,6)
>> >
>> > SET @outTime = @outTime + ' PM'
>> > END
>> >
>> > ELSE
>> > SET @outTime = @outTime + ' AM'
>> >
>> > RETURN @outTime
>> > END
>> >
>> >
>> > "Khurram" <s.khurram@gmai l.com> wrote in
>> > news:ORSxJ5wsEH A.2536@TK2MSFTN GP11.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=testser ver;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.Connecti on = sqlconn
>> >>
>> >> Sqlcom.CommandT ext = m_sql
>> >>
>> >> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
>> >>
>> >> Catch ex As Exception
>> >>
>> >> MsgBox(ex.ToStr ing)
>> >>
>> >> Finally
>> >>
>> >> sqlconn.Dispose ()
>> >>
>> >> Sqlcom.Dispose( )
>> >>
>> >> End Try
>> >>
>> >>
>> >>
>> >> *Please Help me it is Urgent. I am new bee in VB.NET
>> >> Thanks in Advance
>> >>
>> >> Khurram
>> >>
>> >>
>> >>
>> >[/color]
>>
>>[/color]
>[/color]

[cbDevelopment]

Re: Inserting Date or Time into Sql Server Using SQLcommand

What is your locale if not US-en? A perfectly valid response to my post
was that my suggestion was not culture-agnostic.

The code worked fine for me. Try:

m_sql = m_sql & " values ('" & dInDate.ToStrin g & "')"

Is the error a SQL error or a .NET error?


"Khurram" <s.khurram@gmai l.com> wrote in
news:O5V$q1#sEH A.1336@tk2msftn gp13.phx.gbl:
[color=blue]
>
> Hi,
> I tried this code snipset, but it gives me that error, that date is
> out of
> range.
> In all cases it gives me the same error, of out of range, I applied
> three
> differant ways to insert the date and time intot it, but no Luck
>
> Please give any more suggestions??
>
> Thanks in advance
> Dim sTime As String
>
> Dim dInDate As Date
>
> Dim dOutDate As Date
>
> Dim dFromDB As Date
>
> sTime = "4:30:00 pm"
>
> dInDate = Date.Parse(Now. Date.ToShortDat eString & " " & sTime)
>
> Dim m_sql As String
>
> m_sql = "Insert into Logcomms (recdate)"
>
> m_sql = m_sql & " values ('" & CDate(dInDate) & "')" 'First Method
>
> ''''m_sql = m_sql & " values (" & dInDate & ")" 'Second Method
>
> ''''m_sql = m_sql & " values ('" & dInDate & "')" 'Third Method
>
> Sqlcom.Connecti on = sqlconn
>
> Sqlcom.CommandT ext = m_sql
>
> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
>
>
>
> "cbDevelopm ent" <development@re move.700cb.net> wrote in message
> news:Xns95848F1 704A89cbDevelop ment@207.46.248 .16...[color=green]
> > OK, let's try something different. You need to store a time value,
> > but you need the date to always be current? Is that on insert or
> > retrieval? Regardless, it sounds like you want to do this all in
> > VB.
> >
> > Here is some code that will always use the current date and will
> > append whatever time you specify or retrieve to it. Be sure to
> > wrap any date.parse's in try blocks because you may be working with
> > invalid data if it's user-submitted.
> >
> > Hope that helps.
> >
> > ' Insert code
> > Dim sTime As String
> > Dim dInDate As Date
> > Dim dOutDate As Date
> > Dim dFromDB As Date
> >
> > sTime = "4:30:00 pm"
> > dInDate = Date.Parse(Now. Date.ToShortDat eString & " " & sTime)
> >
> > ' Extract code
> > dFromDB = #10/31/2004 6:30:00 PM#
> > dOutDate = Date.Parse(Now. Date.ToShortDat eString & " " &
> > Format(dFromDB, "HH:mm:ss") )
> >
> >
> > "Khurram" <s.khurram@gmai l.com> wrote in
> > news:OqiNwFysEH A.3884@TK2MSFTN GP10.phx.gbl:
> >[color=darkred]
> >> 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
> >>
> >> "cbDevelopm ent" <development@re move.700cb.net> wrote in message
> >> news:Xns9583CCA B13FF3cbDevelop ment@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_TimeOnl y(getdate())
> >> >
> >> > ALTER FUNCTION dbo.udf_TimeOnl y
> >> > (@inDate DATETIME)
> >> > RETURNS VARCHAR(11)
> >> > AS
> >> > BEGIN
> >> > DECLARE @outTime AS VARCHAR(11)
> >> >
> >> > SET @outtime=CONVER T(VARCHAR(8),@i ndate,8)
> >> >
> >> > IF SUBSTRING(@outT ime,1,2)>=12
> >> > BEGIN
> >> > IF SUBSTRING(@outT ime,1,2)>12
> >> > SET @outTime=CAST(( SUBSTRING(@outT ime,1,2)-12) AS VARCHAR) +
> >> > SUBSTRING(@outT ime,3,6)
> >> >
> >> > SET @outTime = @outTime + ' PM'
> >> > END
> >> >
> >> > ELSE
> >> > SET @outTime = @outTime + ' AM'
> >> >
> >> > RETURN @outTime
> >> > END
> >> >
> >> >
> >> > "Khurram" <s.khurram@gmai l.com> wrote in
> >> > news:ORSxJ5wsEH A.2536@TK2MSFTN GP11.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=testser ver;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.Connecti on = sqlconn
> >> >>
> >> >> Sqlcom.CommandT ext = m_sql
> >> >>
> >> >> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
> >> >>
> >> >> Catch ex As Exception
> >> >>
> >> >> MsgBox(ex.ToStr ing)
> >> >>
> >> >> Finally
> >> >>
> >> >> sqlconn.Dispose ()
> >> >>
> >> >> Sqlcom.Dispose( )
> >> >>
> >> >> End Try
> >> >>
> >> >>
> >> >>
> >> >> *Please Help me it is Urgent. I am new bee in VB.NET
> >> >> Thanks in Advance
> >> >>
> >> >> Khurram
> >> >>
> >> >>
> >> >>
> >> >
> >>
> >>[/color]
> >[/color]
>
>
>[/color]

[Khurram]

Re: Inserting Date or Time into Sql Server Using SQLcommand

Thanks for your reply.
My Local is New Zealand. GMT +12

I tried that code too, it gives me that sqlclient Exception.

A conversion from character to Datetime is out of range value.

Any more thought please.

Thanks is advance




"cbDevelopm ent" <development@re move.700cb.net> wrote in message
news:Xns9584E7E F0A05FcbDevelop ment@207.46.248 .16...[color=blue]
> What is your locale if not US-en? A perfectly valid response to my post
> was that my suggestion was not culture-agnostic.
>
> The code worked fine for me. Try:
>
> m_sql = m_sql & " values ('" & dInDate.ToStrin g & "')"
>
> Is the error a SQL error or a .NET error?
>
>
> "Khurram" <s.khurram@gmai l.com> wrote in
> news:O5V$q1#sEH A.1336@tk2msftn gp13.phx.gbl:
>[color=green]
>>
>> Hi,
>> I tried this code snipset, but it gives me that error, that date is
>> out of
>> range.
>> In all cases it gives me the same error, of out of range, I applied
>> three
>> differant ways to insert the date and time intot it, but no Luck
>>
>> Please give any more suggestions??
>>
>> Thanks in advance
>> Dim sTime As String
>>
>> Dim dInDate As Date
>>
>> Dim dOutDate As Date
>>
>> Dim dFromDB As Date
>>
>> sTime = "4:30:00 pm"
>>
>> dInDate = Date.Parse(Now. Date.ToShortDat eString & " " & sTime)
>>
>> Dim m_sql As String
>>
>> m_sql = "Insert into Logcomms (recdate)"
>>
>> m_sql = m_sql & " values ('" & CDate(dInDate) & "')" 'First Method
>>
>> ''''m_sql = m_sql & " values (" & dInDate & ")" 'Second Method
>>
>> ''''m_sql = m_sql & " values ('" & dInDate & "')" 'Third Method
>>
>> Sqlcom.Connecti on = sqlconn
>>
>> Sqlcom.CommandT ext = m_sql
>>
>> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
>>
>>
>>
>> "cbDevelopm ent" <development@re move.700cb.net> wrote in message
>> news:Xns95848F1 704A89cbDevelop ment@207.46.248 .16...[color=darkred]
>> > OK, let's try something different. You need to store a time value,
>> > but you need the date to always be current? Is that on insert or
>> > retrieval? Regardless, it sounds like you want to do this all in
>> > VB.
>> >
>> > Here is some code that will always use the current date and will
>> > append whatever time you specify or retrieve to it. Be sure to
>> > wrap any date.parse's in try blocks because you may be working with
>> > invalid data if it's user-submitted.
>> >
>> > Hope that helps.
>> >
>> > ' Insert code
>> > Dim sTime As String
>> > Dim dInDate As Date
>> > Dim dOutDate As Date
>> > Dim dFromDB As Date
>> >
>> > sTime = "4:30:00 pm"
>> > dInDate = Date.Parse(Now. Date.ToShortDat eString & " " & sTime)
>> >
>> > ' Extract code
>> > dFromDB = #10/31/2004 6:30:00 PM#
>> > dOutDate = Date.Parse(Now. Date.ToShortDat eString & " " &
>> > Format(dFromDB, "HH:mm:ss") )
>> >
>> >
>> > "Khurram" <s.khurram@gmai l.com> wrote in
>> > news:OqiNwFysEH A.3884@TK2MSFTN GP10.phx.gbl:
>> >
>> >> 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
>> >>
>> >> "cbDevelopm ent" <development@re move.700cb.net> wrote in message
>> >> news:Xns9583CCA B13FF3cbDevelop ment@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_TimeOnl y(getdate())
>> >> >
>> >> > ALTER FUNCTION dbo.udf_TimeOnl y
>> >> > (@inDate DATETIME)
>> >> > RETURNS VARCHAR(11)
>> >> > AS
>> >> > BEGIN
>> >> > DECLARE @outTime AS VARCHAR(11)
>> >> >
>> >> > SET @outtime=CONVER T(VARCHAR(8),@i ndate,8)
>> >> >
>> >> > IF SUBSTRING(@outT ime,1,2)>=12
>> >> > BEGIN
>> >> > IF SUBSTRING(@outT ime,1,2)>12
>> >> > SET @outTime=CAST(( SUBSTRING(@outT ime,1,2)-12) AS VARCHAR) +
>> >> > SUBSTRING(@outT ime,3,6)
>> >> >
>> >> > SET @outTime = @outTime + ' PM'
>> >> > END
>> >> >
>> >> > ELSE
>> >> > SET @outTime = @outTime + ' AM'
>> >> >
>> >> > RETURN @outTime
>> >> > END
>> >> >
>> >> >
>> >> > "Khurram" <s.khurram@gmai l.com> wrote in
>> >> > news:ORSxJ5wsEH A.2536@TK2MSFTN GP11.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=testser ver;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.Connecti on = sqlconn
>> >> >>
>> >> >> Sqlcom.CommandT ext = m_sql
>> >> >>
>> >> >> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
>> >> >>
>> >> >> Catch ex As Exception
>> >> >>
>> >> >> MsgBox(ex.ToStr ing)
>> >> >>
>> >> >> Finally
>> >> >>
>> >> >> sqlconn.Dispose ()
>> >> >>
>> >> >> Sqlcom.Dispose( )
>> >> >>
>> >> >> End Try
>> >> >>
>> >> >>
>> >> >>
>> >> >> *Please Help me it is Urgent. I am new bee in VB.NET
>> >> >> Thanks in Advance
>> >> >>
>> >> >> Khurram
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >>
>> >>
>> >[/color]
>>
>>
>>[/color]
>[/color]

[cbDevelopment]

Re: Inserting Date or Time into Sql Server Using SQLcommand

OK, so you are in a locale that formats dates as DD/MM/YYYY. It sounds
like your SQL Server and your application server might have different
regional settings. the .ToString method formats the date to the current
locale and we did that properly, but the server isn't accepting it.

If this is the problem, this might help:

imports system.globaliz ation
imports system.threadin g

Dim sTime As String
Dim dInDate As Date
Dim dOutDate As Date
Dim dFromDB As Date
Dim m_sql As String
dim ciOriginal as cultureinfo
dim ciDatabase as cultureinfo

' Define the current date settings and the database settings
ciOriginal = thread.currentt hread.currentcu lture
ciDatabase = CultureInfo.Cre ateSpecificCult ure("en-US")

' Set the current thread to use the db settings
Thread.CurrentT hread.CurrentCu lture = ciDatabase

sTime = "4:30:00 pm"
dInDate = Date.Parse(Now. Date.ToShortDat eString & " " & sTime)
m_sql = "Insert into Logcomms (recdate)"
m_sql = m_sql & " values ('" & CDate(dInDate) & "')" 'First

Sqlcom.Connecti on = sqlconn
Sqlcom.CommandT ext = m_sql

Dim M_result As Integer = Sqlcom.ExecuteN onQuery

' return to original settings
Thread.CurrentT hread.CurrentCu lture = ciOriginal


And Cor, if you're watching, you're probably laughing right now...


"Khurram" <s.khurram@gmai l.com> wrote in
news:OcTAln$sEH A.3772@TK2MSFTN GP10.phx.gbl:
[color=blue]
> Thanks for your reply.
> My Local is New Zealand. GMT +12
>
> I tried that code too, it gives me that sqlclient Exception.
>
> A conversion from character to Datetime is out of range value.
>
> Any more thought please.
>
> Thanks is advance
>
>
>
>
> "cbDevelopm ent" <development@re move.700cb.net> wrote in message
> news:Xns9584E7E F0A05FcbDevelop ment@207.46.248 .16...[color=green]
> > What is your locale if not US-en? A perfectly valid response to my
> > post was that my suggestion was not culture-agnostic.
> >
> > The code worked fine for me. Try:
> >
> > m_sql = m_sql & " values ('" & dInDate.ToStrin g & "')"
> >
> > Is the error a SQL error or a .NET error?
> >
> >
> > "Khurram" <s.khurram@gmai l.com> wrote in
> > news:O5V$q1#sEH A.1336@tk2msftn gp13.phx.gbl:
> >[color=darkred]
> >>[/color][/color][/color]

[Khurram]

Re: Inserting Date or Time into Sql Server Using SQLcommand

Thanks a Lot cbDevelopment.
It works like Magic ;) Thanks a lot again, with heaps of prayers for you
all who helped me
Now more over to that, I want to insert the Time Value in my database.
I can't use the Char Format, I have to use the Same datetime field, but
the data I require in that that is Time Only.
I tried only to insert the Time, but It Insert Date with it.
can you give me any idea, that how can I insert only Time value without
date.
i tested it with Old VB6 code, and it works and inserted only timevalue.

But in VB.net I can't.
I hope you must have solution for that too.

Thanks in advance.

Khurram





"cbDevelopm ent" <development@re move.700cb.net> wrote in message
news:Xns9585695 1548FCcbDevelop ment@207.46.248 .16...[color=blue]
> OK, so you are in a locale that formats dates as DD/MM/YYYY. It sounds
> like your SQL Server and your application server might have different
> regional settings. the .ToString method formats the date to the current
> locale and we did that properly, but the server isn't accepting it.
>
> If this is the problem, this might help:
>
> imports system.globaliz ation
> imports system.threadin g
>
> Dim sTime As String
> Dim dInDate As Date
> Dim dOutDate As Date
> Dim dFromDB As Date
> Dim m_sql As String
> dim ciOriginal as cultureinfo
> dim ciDatabase as cultureinfo
>
> ' Define the current date settings and the database settings
> ciOriginal = thread.currentt hread.currentcu lture
> ciDatabase = CultureInfo.Cre ateSpecificCult ure("en-US")
>
> ' Set the current thread to use the db settings
> Thread.CurrentT hread.CurrentCu lture = ciDatabase
>
> sTime = "4:30:00 pm"
> dInDate = Date.Parse(Now. Date.ToShortDat eString & " " & sTime)
> m_sql = "Insert into Logcomms (recdate)"
> m_sql = m_sql & " values ('" & CDate(dInDate) & "')" 'First
>
> Sqlcom.Connecti on = sqlconn
> Sqlcom.CommandT ext = m_sql
>
> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
>
> ' return to original settings
> Thread.CurrentT hread.CurrentCu lture = ciOriginal
>
>
> And Cor, if you're watching, you're probably laughing right now...
>
>
> "Khurram" <s.khurram@gmai l.com> wrote in
> news:OcTAln$sEH A.3772@TK2MSFTN GP10.phx.gbl:
>[color=green]
>> Thanks for your reply.
>> My Local is New Zealand. GMT +12
>>
>> I tried that code too, it gives me that sqlclient Exception.
>>
>> A conversion from character to Datetime is out of range value.
>>
>> Any more thought please.
>>
>> Thanks is advance
>>
>>
>>
>>
>> "cbDevelopm ent" <development@re move.700cb.net> wrote in message
>> news:Xns9584E7E F0A05FcbDevelop ment@207.46.248 .16...[color=darkred]
>> > What is your locale if not US-en? A perfectly valid response to my
>> > post was that my suggestion was not culture-agnostic.
>> >
>> > The code worked fine for me. Try:
>> >
>> > m_sql = m_sql & " values ('" & dInDate.ToStrin g & "')"
>> >
>> > Is the error a SQL error or a .NET error?
>> >
>> >
>> > "Khurram" <s.khurram@gmai l.com> wrote in
>> > news:O5V$q1#sEH A.1336@tk2msftn gp13.phx.gbl:
>> >
>> >>[/color][/color][/color]

[Khurram]

Re: Inserting Date or Time into Sql Server Using SQLcommand

Thanks David,
As I already told you that My Application was already Built In In VB6
code, and there are millions of records already entered into the database.
And also My Clietn application is Running in More than Ten Departments for
data entering.
So in case of change the DateTime field to Char will ended up to change
the Time value as a Charachter value.
AnyWay Thanks for you Idea.



"David Fúnez" <dfunez@hotmail .com> wrote in message
news:e38PzX6sEH A.1400@TK2MSFTN GP11.phx.gbl...[color=blue]
> If you just need the Time why are you using a DateTime Field...?
> instead use a Char field, take the part of the Time you want save an[/color]
put it[color=blue]
> in a Char field.
>
> Have a nice day.
>
> --
> David Fúnez
> Tegucigalpa, Honduras
>
> "La Piratería Mata Las Ideas"
> Desarrollador Cinco Estrellas Visual FoxPro
>
>
> "Khurram" <s.khurram@gmai l.com> wrote in message
> news:ORSxJ5wsEH A.2536@TK2MSFTN GP11.phx.gbl...[color=green]
>> 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[/color][/color]
SQL[color=blue][color=green]
>> Date time Field.
>> I tried to many ways, I can extract the value in timeonly format by[/color]
> using[color=green]
>> this command
>> Format(now,"HH: mm:ss")
>> But when I insert it into the Sql Server database, it embadded date[/color]
> value[color=green]
>> 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[/color]
> date[color=green]
>> along with time. and If I want then I already passed the Current Date,[/color][/color]
so[color=blue][color=green]
>> the current date should be displayed, but why it is displaying the[/color][/color]
default[color=blue][color=green]
>> date "01/01/1900".
>>
>> More over I checked to insert the simple current date into the[/color][/color]
database[color=blue][color=green]
>> 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=testser ver;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.Connecti on = sqlconn
>>
>> Sqlcom.CommandT ext = m_sql
>>
>> Dim M_result As Integer = Sqlcom.ExecuteN onQuery
>>
>> Catch ex As Exception
>>
>> MsgBox(ex.ToStr ing)
>>
>> Finally
>>
>> sqlconn.Dispose ()
>>
>> Sqlcom.Dispose( )
>>
>> End Try
>>
>>
>>
>> *Please Help me it is Urgent. I am new bee in VB.NET
>> Thanks in Advance
>>
>> Khurram
>>
>>[/color]
>
>[/color]

[No Sheds]

Re: Inserting Date or Time into Sql Server Using SQLcommand

In article <u183wCItEHA.38 72@TK2MSFTNGP15 .phx.gbl>, Khurram
<s.khurram@gmai l.com> writes[color=blue]
> Thanks a Lot cbDevelopment.
> It works like Magic ;) Thanks a lot again, with heaps of prayers for you
>all who helped me[/color]

If you use a Stored Procedure that accepts parameters to pass the date,
you shouldn't need to do any adjustment of the date, no matter what time
zone you're in, as you pass it the variable, not a string representation
of a date.

I prefer these because:
a) I think they're faster
b) They're more secure and
c) You can pass things like dates or names like O'Brien to them without
having to do stuff like replace ' with ''.

Sorry, I don't have time to post an example here, but look up
parameterized queries or some such and you should find enough help.

If you really must build SQL "on the fly", you can even write it to
include parameters.
[color=blue]
> Now more over to that, I want to insert the Time Value in my database.
> I can't use the Char Format, I have to use the Same datetime field, but
>the data I require in that that is Time Only.
> I tried only to insert the Time, but It Insert Date with it.
> can you give me any idea, that how can I insert only Time value without
>date.
> i tested it with Old VB6 code, and it works and inserted only timevalue.[/color]

Repeating what has already been said, in a different way:
The DateTime field in SQL Server holds a date and a time. Period.

If you pass it just a time, SQL Server will store this time, but will
add a default date to it e.g. 1/1/1900

This is the same for VB6 as for VB.NET.

If you're only interested in the date, just pass a date to SQL Server.
If you read the data back, ignore the date part and use only the time
part.

HTH
--
No Sheds