Web application - return dates in specific format via ActiveX SQL Server ADODB.Recordset

Hello,

We have a legacy 32-bit web application that is now running on Windows 2008 64-Bit server and accessing an sql2005 database on a different server.  The asp pages interact with a Visual Basic 5.0 activex that retrieves SQL data using ADODB.Recordset when an initilization string of something like
objRecordset.Open sqlString, "Driver={SQL Server}; Server=SERVER\MSSQL2005;Database=sfiweb; UID=xxortal; PWD=xxportal", 1, 1, &H1

Now that we have moved this app to a new webserver and database sercer, I am finding that the data returned to the web application from the SQL2005 is returning dates in dd/mm/yyyy hh:mm:nn format.

So for example a Select statement passed to the activex control:
select * from web_logging
is returning SQL datetime fields in the wrong format, the expected format is what is in the database e.g. yyyy-mm-dd hh:mm:nn etc

If I am on the server itself and perform a select * the dates are displayed in the correct format.

Is there a setting to ensure that the date formats match so that further statements using the selected date fields are in the format expected.

Thank-you.
LVL 1
jcwiatrAsked:
Who is Participating?
 
kemi67Commented:
The format of date fields, if not specified in the select query, is given by the client international date settings.
In your case the client is the web server.
You can resolve your problem in diffent ways
1)Check international settings in the control panel of the web server.
2) otherwise, you should modify all your query that returns a date as here
http://www.sql-server-helper.com/tips/date-formats.aspx
For example
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
3)I am not sure if you can specify a date format (international settings) in the ADO connection string: I will check
0
 
kemi67Commented:
You can even set you LCID in your web application
Read here for reference
http://4guysfromrolla.com/webtech/041001-1.shtml

For example
'English - United States LCID
Session.LCID = 1033
Response.Write Date() & "<br />"

'English - Australia LCID
Session.LCID = 3081
Response.Write Date() & "<br />"
0
 
kemi67Commented:
Regarding the ado connection string, try to insert event the "Locale Identifier" parameter
For united states:
objRecordset.Open sqlString, "Driver={SQL Server}; Server=SERVER\MSSQL2005;Database=sfiweb; Locale Identifier=1033;UID=xxortal; PWD=xxportal", 1, 1, &H1
0
 
jcwiatrAuthor Commented:
Hi there thanks very much.  I have put in the recommendation to change the select statements to always return US formatted dates.  In the interim I have changed the system/network service accounts on the web server to have regional setting of english(us) to get over the hurdle.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.