Help please!
I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).
the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?
i.e.
if request("dob") is "01/11/2007" (1st november 2007)
set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
rs.open "tez", mc, 2, 2 rs.addnew
rs("dob") = request("dob")
rs.update
11 jan 2007 stored in table
while
set trs = Server.CreateObject("ADODB.RecordSet")
qfn= "insert tez values('"+request("dob")+"')"
trs.Open qfn,mc
results in
1 november 2007 is written to the table.
Both of these methods are used in the same asp page.
This is on a windows2003 server, sql2005,iisv6, asp.netv2
I have tried every setting I can find in iis,asp,sql server to no avail.
I need the recordset method to work correctly.
Terry
It is a 'best practice' to use ISO formatted dates when working with systems that are in different Regions, etc.
Have the application submit the date to the data server in the form or "yyyy/mm/dd".
|||Hi,
If you are using the datetime data type, SQL Server stores internally and transfers to clients (TDS) the date in an internal format, which would always be the same. The final representation which you get would eventually depend on the client and the client API. For instance, in sqlcmd.exe you could insert something like:
insert mytab values ('July 3 2006')
(note that you could control the format using commands like SET DATEFORMAT, etc)
But if you use an ADO script like this:
Dim cn
Set cn=CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=<myserver>;initial catalog=<mydb>"
Dim rs
Set rs=cn.Execute("SELECT * FROM master..mytab")
MsgBox rs.Fields("d")
rs.Close
cn.Close
the results would be:
7/3/2006
How do you query/display the value? It might also sometimes depend on the regional settings of your machine.
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Unfortunatly this is not my application so I can not change the way dates are submitted, I am supporting an application that, when a date is input the day and month of the date are reversed.
This is an issue with a setting on either our sql2005 sever setup, our iis setup or maybee a .net issues.
I have checked every thing I can think of to no avail.
During my investigation I notice the symptons as described, that on the same asp page, with the same connection object an "insert <table> values(<mydate>)" slq command works correctly while a recordset.add does not.!
|||Yes the table is using a datetime data type, but for the same connection object on the same asp page,
an "insert <mytable> values('1/10/2005')"
inserts '1 october 2005' into the table
while an
recordset.open
recordset.add
recordset.datefield = '1/10/2005'
inserts '10 january 2005' into the table !?
terry
|||Those problems with datetimes in old Visual Basic and ASP... That's why is best practice to keep an standard.
Your problem is that the code converts strings to datetime in two different languages and environments, that have different settings:
1 - In Visual Basic Script (ASP), you convert a string (from the request object), to a datetime.
rs("dob") = request("dob")
From http://msdn2.microsoft.com/en-us/library/3eaydw6e(VS.80).aspx, Visual Basic in general and VBScript in particular, follows the convention of M/d/yyyy, always. So if request("dob") is "01/11/2007", VBScript takes it as "January 11, 2007" in its internal datetime representation, which is sent to the server not as a string, but as an integer representing datetime. No Regional Settings are used.
2 - But when you send SQL to the server, the conversion depends on your database and server configuration. So the code:
set trs = Server.CreateObject("ADODB.RecordSet")
qfn= "insert tez values('"+request("dob")+"')"
trs.Open qfn,mc
Creates for example the SQL
insert tez values('01/11/2007')
Suppose this SQL is processed by your SQL Server 2005, default installation on a server that has for Regional Settings the format "D/M/Y" (UK for example), then SQL Server converts using the regional settings, and thus the value inserted is equivalent to "November 1, 2007"
Now that you know the root cause, there are two solutions:
1 - The easiest and quicker workaround, but limited: Change the Regional Settings in your server, to United States (the equivalent to the behavior hardcoded in Visual Basic). That will solve this problem, but may break another application not correctly implemented doing the same but with other format assumptions...
2 - The better, portable workaround: Never use VB code for converting a string to a datetime; send the string to the database, and make the database server convert the data (if possible explicitly, for example by using "set dateformat dmy" in SQL Server).
So, change all your client recordsets, to server recordsets, and send the dates in a defined, standard format that your app converts. It is also a good practice to send it to stored procedures, which define the dateformat explicitly.
Note that the recommended solution (but more work) is #2, because if you take #1 and then you have to move the same code to another server (which may not be in your control, maybe hosted on a different country), it could break again.
Bruno Guardia - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Could anybody help Please,
I have application running on Windows XP which uses the date format of en-US(MM\DD\YYYY),But when I shift the application to windows 2003 server with date format of Germany it fails to work.Even i tried to change the language from Germany to English -united state even then it is taking Germany format of date itself.(DD.MM.YYYY)
|||are you using SQL Server? If so, you should apply the solution described (use a consistent data format, independent of regional settings)
No comments:
Post a Comment