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]

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

 

[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

 

[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

 

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

 

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

 

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

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]

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]

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]

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

 

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

 

[No Sheds]

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

 

[Khurram]

Thanks a Lot. I got it now.

Thanks you very Very Much