Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-22
4
Medium Priority
?
417 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
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.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

670 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