[RESOLVED] Sql server update date with wrong value

[magnitude]

hello all

i have this code code that update insert record in the SQL Server database

Dim rs As New SqlCommand("INSERT INTO " & sTable & " (TireID,RFID,Model,[Tire Size],InitialBrandingDate,Installed,Location,[Recommended Inflation]) Values('" & sValues(0) & "','" & sValues(1) & "','" & sValues(2) & "','" & sValues(3) & "','" & DateTime.Now & "',0,'" & _DEFAULT_LOCATION & "'," & Psi & ")", _DBConn_LOCAL)
 rs.ExecuteNonQuery

I used the datetime.now to get the current date and time value to be saved in the database. When I checked the value inserted into the server the InitialBrandingDate value has different value than i expected.

when I used msgbox(datetime.now) it shows 4/30/2013 3:56:26 PM but the server has this value "2013-4-30 6:56:26.000" when i Tried to call this value from the server using mm/dd/yyyy hh:mm tt format it is showing " 04/30/2013 6:56 AM"

I dont know what is going on please help.

Thank you.

[dunfiddlin]

Evidently the server is operating on a different time zone (or just a really badly maintained clock) to your program. You need to remember that date variables are just big numbers in fact. They have to be interpreted to give you the formatted values you see. So if your server and the client are not synchronised you will only ever get what time that variable represents to the server irrespective of what time you think you've passed it.

[jmcilhinney]

Firstly, fix your code. DO NOT use string concatenation to insert values into SQL code. Always use parameters. To learn why and how to do that, follow the Blog link in my signature and check out my post on Parameters In ADO.NET. Once you're doing things the proper way, let's see if the issue remains.

dunfiddlin's explanation doesn't really seem to work in this case because it's the client date/time that's being saved, not the server date/time. Whether or not your server is in a different time zone is irrelevant because it should just be saving the value that you send it.

[sapator]

You can always format your value inside the sql server with a stored procedure, that way if you get a wrong value you will know that it is from the source.
Now you said that you tried to call with mm/dd/yyyy hh:mm tt and you got " 04/30/2013 6:56 AM" . Well that is the correct formatting you will get, the exact format you want it to appear depends entirely on you. I am not sure what is the exact format you want btw, you do not specify what you want, or i missed something.

[magnitude]

thanks I fixed the issue, the problem was the timezone settings in the client machine is wrong, although the client machine is showing the local time but the sql server interpret it based on the timezone it was created.