Solved

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

Posted on 2011-02-22
4
399 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article you will learn how to create a free basic website on Bitbucket, a git service provider. Polymer creates dynamic HTML components, which allow more flexibility than static HTML. This tutorial uses Ubuntu Linux but can also be done on W…
Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
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…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now