Solved

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

Posted on 2011-02-22
4
403 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Report: Liquid Web beats Amazon, Rackspace & More

A study by performance analyst firm Cloud Spectator finds that Liquid Web beats rivals Amazon, Rackspace and DigitalOcean when it comes to website and cloud application performance.

Question has a verified solution.

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

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

734 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