Solved

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

Posted on 2011-02-22
4
401 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:jcwiatr
  • 3
4 Comments
 
LVL 7

Accepted Solution

by:
kemi67 earned 500 total points
ID: 34951674
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
 
LVL 7

Expert Comment

by:kemi67
ID: 34951839
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
 
LVL 7

Expert Comment

by:kemi67
ID: 34951946
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
 
LVL 1

Author Comment

by:jcwiatr
ID: 34956240
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question