 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / VB.NET / October 2004Inserting Date or Time into Sql Server Using SQLcommand
I have a problem while inserting time value in the datetime Field. I want to Insert only time value in this format (08:15:39) into the SQL Date time Field. I tried to many ways, I can extract the value in timeonly format by using this command Format(now,"HH:mm:ss") But when I insert it into the Sql Server database, it embadded date value with it. the output looks like that "01/01/1900 08:59:00" in that case time is correct but date is picked up by default, but I don't even want to have date along with time. and If I want then I already passed the Current Date, so the current date should be displayed, but why it is displaying the default date "01/01/1900". More over I checked to insert the simple current date into the database with the Insert command, but it still inserts the Default date. Here is my complete code. Dim sqlconn As New SqlConnection("Data Source =MSC RHD SERVER;user id =sa;pwd=testserver;initial catalog = testData") Dim Sqlcom As New SqlCommand Try sqlconn.Open() Dim m_sql As String m_sql = "Insert into Logcomms (recdate)" m_sql = m_sql & " values (" & DateTime.Today & ")" 'MsgBox(m_sql) Sqlcom.Connection = sqlconn Sqlcom.CommandText = m_sql Dim M_result As Integer = Sqlcom.ExecuteNonQuery Catch ex As Exception MsgBox(ex.ToString) Finally sqlconn.Dispose() Sqlcom.Dispose() End Try *Please Help me it is Urgent. I am new bee in VB.NET Thanks in Advance Khurram This is "just how it is". The DateTime datatype in SQL Server is just that, date and time. 1/1/1900 is the beginning of time with SQL Server, so you can just strip it out or ignore it. That's probably not what you wanted, so here is a SQL function to return only the time. Use like: select dbo.udf_TimeOnly(getdate()) ALTER FUNCTION dbo.udf_TimeOnly (@inDate DATETIME) RETURNS VARCHAR(11) AS BEGIN DECLARE @outTime AS VARCHAR(11) SET @outtime=CONVERT(VARCHAR(8),@indate,8) IF SUBSTRING(@outTime,1,2)>=12 BEGIN IF SUBSTRING(@outTime,1,2)>12 SET @outTime=CAST((SUBSTRING(@outTime,1,2)-12) AS VARCHAR) + SUBSTRING(@outTime,3,6) SET @outTime = @outTime + ' PM' END ELSE SET @outTime = @outTime + ' AM' RETURN @outTime END > I have a problem while inserting time value in the datetime Field. > [quoted text clipped - 59 lines] > > 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 > 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.khurram@gmail.com> wrote in > news:ORSxJ5wsEHA.2536@TK2MSFTNGP11.phx.gbl: > >> I have a problem while inserting time value in the datetime Field. >> >> I want to Insert only time value in this format (08:15:39) into the >> SQL >> Date time Field. >> I tried to many ways, I can extract the value in timeonly format by >> using >> this command >> Format(now,"HH:mm:ss") >> But when I insert it into the Sql Server database, it embadded date >> value >> with it. >> the output looks like that "01/01/1900 08:59:00" in that case time >> is >> correct but date is picked up by default, but I don't even want to >> have date along with time. and If I want then I already passed the >> Current Date, so the current date should be displayed, but why it is >> displaying the default date "01/01/1900". >> >> More over I checked to insert the simple current date into the >> database >> with the Insert command, but it still inserts the Default date. >> Here is my complete code. >> Dim sqlconn As New SqlConnection("Data Source =MSC RHD SERVER;user >> id >> =sa;pwd=testserver;initial catalog = testData") >> >> Dim Sqlcom As New SqlCommand >> >> Try >> >> sqlconn.Open() >> >> Dim m_sql As String >> >> m_sql = "Insert into Logcomms (recdate)" >> >> m_sql = m_sql & " values (" & DateTime.Today & ")" >> >> 'MsgBox(m_sql) >> >> Sqlcom.Connection = sqlconn >> >> Sqlcom.CommandText = m_sql >> >> Dim M_result As Integer = Sqlcom.ExecuteNonQuery >> >> Catch ex As Exception >> >> MsgBox(ex.ToString) >> >> Finally >> >> sqlconn.Dispose() >> >> Sqlcom.Dispose() >> >> End Try >> >> >> >> *Please Help me it is Urgent. I am new bee in VB.NET >> Thanks in Advance >> >> Khurram Khuram, In addition to cbDevelompment, you can not insert only a time in an SQL server in datetime format, because there is not a Time format. You can translate it to a long or whatever other value, however I would not do it. A DateTime with 01/01/1900 means crazy enough a time withouth a date. Just to give you from someone else the same answer as cbDevelopment. Cor "Khurram" <s.khurram@gmail.com> .. > Thanks a lot. > But My Requirement is to insert the Time or date with the Current System [quoted text clipped - 105 lines] > >> > >> Khurram 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")) > Thanks a lot. > But My Requirement is to insert the Time or date with the Current [quoted text clipped - 106 lines] > >> > >> Khurram 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 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. > cb Develompment, > [quoted text clipped - 3 lines] > > Cor 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 > 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.khurram@gmail.com> wrote in > news:OqiNwFysEHA.3884@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" <development@remove.700cb.net> wrote in message >> news:Xns9583CCAB13FF3cbDevelopment@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.khurram@gmail.com> wrote in >> > news:ORSxJ5wsEHA.2536@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 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? > Hi, > I tried this code snipset, but it gives me that error, that date is [quoted text clipped - 179 lines] > >> >> > >> >> 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 > 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.khurram@gmail.com> wrote in > news:O5V$q1#sEHA.1336@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" <development@remove.700cb.net> wrote in message >> news:Xns95848F1704A89cbDevelopment@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.khurram@gmail.com> wrote in >> > news:OqiNwFysEHA.3884@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" <development@remove.700cb.net> wrote in message >> >> news:Xns9583CCAB13FF3cbDevelopment@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.khurram@gmail.com> wrote in >> >> > news:ORSxJ5wsEHA.2536@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 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... > Thanks for your reply. > My Local is New Zealand. GMT +12 [quoted text clipped - 19 lines] > > "Khurram" <s.khurram@gmail.com> wrote in > > news:O5V$q1#sEHA.1336@tk2msftngp13.phx.gbl: 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 > 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.khurram@gmail.com> wrote in > news:OcTAln$sEHA.3772@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" <development@remove.700cb.net> wrote in message >> news:Xns9584E7EF0A05FcbDevelopment@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.khurram@gmail.com> wrote in >> > news:O5V$q1#sEHA.1336@tk2msftngp13.phx.gbl: > 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 [quoted text clipped - 3 lines] >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  SignatureNo Sheds Thanks a Lot. I got it now. Thanks you very Very Much > In article <u183wCItEHA.3872@TK2MSFTNGP15.phx.gbl>, Khurram > <s.khurram@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 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. SignatureDavid F?nez Tegucigalpa, Honduras "La Pirater?a Mata Las Ideas" Desarrollador Cinco Estrellas Visual FoxPro > I have a problem while inserting time value in the datetime Field. > [quoted text clipped - 53 lines] > > 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. > 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@gmail.com> wrote in message > news:ORSxJ5wsEHA.2536@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 Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.