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=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

[cbDevelopment]

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

[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

[Cor Ligthert]

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
>>
>>
>>

>

[David Fúnez]

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.*******@gmail.com> wrote in message
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

[cbDevelopment]

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.ToShortDateString & " " & sTime)

' Extract code
dFromDB = #10/31/2004 6:30:00 PM#
dOutDate = Date.Parse(Now.Date.ToShortDateString & " " & Format(dFromDB,
"HH:mm:ss"))
"Khurram" <s.*******@gmail.com> wrote in
news:Oq**************@TK2MSFTNGP10.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

"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
>>
>>
>>

>

[Cor Ligthert]

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]

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" <no************@planet.nl> wrote in
news:u6*************@TK2MSFTNGP15.phx.gbl:

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

[Khurram]

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.ToShortDateString & " " & 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.Connection = sqlconn

Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

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

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.ToShortDateString & " " & sTime)

' Extract code
dFromDB = #10/31/2004 6:30:00 PM#
dOutDate = Date.Parse(Now.Date.ToShortDateString & " " & Format(dFromDB,
"HH:mm:ss"))
"Khurram" <s.*******@gmail.com> wrote in
news:Oq**************@TK2MSFTNGP10.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

"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
>>
>>
>>
>

[cbDevelopment]

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.ToString & "')"

Is the error a SQL error or a .NET error?
"Khurram" <s.*******@gmail.com> wrote in
news:O5**************@tk2msftngp13.phx.gbl:

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.ToShortDateString & " " & 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.Connection = sqlconn

Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

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

> 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.ToShortDateString & " " & sTime)
>
> ' Extract code
> dFromDB = #10/31/2004 6:30:00 PM#
> dOutDate = Date.Parse(Now.Date.ToShortDateString & " " &
> Format(dFromDB, "HH:mm:ss"))
>
>
> "Khurram" <s.*******@gmail.com> wrote in
> news:Oq**************@TK2MSFTNGP10.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
>>
>> "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
>> >>
>> >>
>> >>
>> >
>>
>>

>

[Khurram]

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


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

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.ToString & "')"

Is the error a SQL error or a .NET error?
"Khurram" <s.*******@gmail.com> wrote in
news:O5**************@tk2msftngp13.phx.gbl:

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.ToShortDateString & " " & 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.Connection = sqlconn

Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

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

> 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.ToShortDateString & " " & sTime)
>
> ' Extract code
> dFromDB = #10/31/2004 6:30:00 PM#
> dOutDate = Date.Parse(Now.Date.ToShortDateString & " " &
> Format(dFromDB, "HH:mm:ss"))
>
>
> "Khurram" <s.*******@gmail.com> wrote in
> news:Oq**************@TK2MSFTNGP10.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
>>
>> "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
>> >>
>> >>
>> >>
>> >
>>
>>
>

[cbDevelopment]

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.globalization
imports system.threading

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.currentthread.currentculture
ciDatabase = CultureInfo.CreateSpecificCulture("en-US")

' Set the current thread to use the db settings
Thread.CurrentThread.CurrentCulture = ciDatabase

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

Sqlcom.Connection = sqlconn
Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

' return to original settings
Thread.CurrentThread.CurrentCulture = ciOriginal
And Cor, if you're watching, you're probably laughing right now...
"Khurram" <s.*******@gmail.com> wrote in
news:Oc**************@TK2MSFTNGP10.phx.gbl:

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


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

> 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.ToString & "')"
>
> Is the error a SQL error or a .NET error?
>
>
> "Khurram" <s.*******@gmail.com> wrote in
> news:O5**************@tk2msftngp13.phx.gbl:
>

>>

[Khurram]

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

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

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.globalization
imports system.threading

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.currentthread.currentculture
ciDatabase = CultureInfo.CreateSpecificCulture("en-US")

' Set the current thread to use the db settings
Thread.CurrentThread.CurrentCulture = ciDatabase

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

Sqlcom.Connection = sqlconn
Sqlcom.CommandText = m_sql

Dim M_result As Integer = Sqlcom.ExecuteNonQuery

' return to original settings
Thread.CurrentThread.CurrentCulture = ciOriginal
And Cor, if you're watching, you're probably laughing right now...
"Khurram" <s.*******@gmail.com> wrote in
news:Oc**************@TK2MSFTNGP10.phx.gbl:

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


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

> 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.ToString & "')"
>
> Is the error a SQL error or a .NET error?
>
>
> "Khurram" <s.*******@gmail.com> wrote in
> news:O5**************@tk2msftngp13.phx.gbl:
>
>>

[Khurram]

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" <df****@hotmail.com> wrote in message
news:e3**************@TK2MSFTNGP11.phx.gbl...

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.*******@gmail.com> wrote in message
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

[No Sheds]

In article <u1**************@TK2MSFTNGP15.phx.gbl>, Khurram
<s.*******@gmail.com> writes

Thanks a Lot cbDevelopment.
It works like Magic ;) Thanks a lot again, with heaps of prayers for you
all who helped me
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.
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.

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

[Khurram]

Thanks a Lot. I got it now.

Thanks you very Very Much

"No Sheds" <[e-mail]> wrote in message
news:E9**************@hst.me.uk...

In article <u1**************@TK2MSFTNGP15.phx.gbl>, Khurram
<s.*******@gmail.com> writes

Thanks a Lot cbDevelopment.
It works like Magic ;) Thanks a lot again, with heaps of prayers for youall who helped me
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.

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, butthe 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 withoutdate.
i tested it with Old VB6 code, and it works and inserted only

timevalue.
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