Brain Drain - Date - New Server - US / ISO / UK
-
Hi
I have just had the "pleasure" of moving an old ASP website / SQL DB to a new dedicated OVH server.
The DB has moved from SQL 2005 to SQL 2012 (web edition 64 bit).
I am having the old issue of date formats showing up as US format on the website e.g 8/3/2016 instead of 03/08/2016.
I enter the dates on the ASP CLASSIC, website as 03/08/2016 and convert them to ISO format 2016-08-03 in the SQL that is passed to the Stored Procedure that has SET DATEFORMAT YMD at the top of it.
I have made sure all the SQL Logins to the DB have "British English" selected as their "Default Language".
If I view the database properties under options the Default Language is British English.
If I view the server properties under General->Language it's English (United States) but under Advanced->Default Language it's British English.
The dates are getting stored as ISO correctly as if I do a DATEDIFF(DAY,Stamp,GETDATE())=0 I can see all the records even though they are showing up on the website as US format 8/3/2016 (Why there are no zeros in front I don't know)
The ASP code hasn't changed or the DB code it was just ported into this new dedicated server and now I am getting these issues. I am sure I solved something like this ages ago just by changing the default login language but that doesn't seem to work.
I am getting lots of Primary Key/Index errors due to duplicate insertions due to the dates (mixing up US/UK) from a .NET app I have that uses the Betfair API to get racing data e.g
EXEC dbo.usp_net_insert_betfair_market_selection @MarketID = 125932808, @SelectionID = 10593225, @Racedatetime = '2016-08-03 15:10:00', @MarketType = 'WIN', @HorseName = 'She Done Good';
Violation of PRIMARY KEY constraint 'PK_BETFAIR_MARKET_SELECTIONS'. Cannot insert duplicate key in object 'dbo.BETFAIR_MARKET_SELECTIONS'. The duplicate key value is (719859, WIN, Mar 8 2016 3:10PM).
However if I copy that EXEC statement and run it direct in a query analyser window it runs WITHOUT ANY ERROR.
I have been googling my head off and I have seen someone suggest putting this code at the top of all ASP pages that show dates to force it show in UK format > https://www.webwiz.co.uk/kb/asp-tutorials/date-time-settings.htm
'* Set the server locale to UK
Session.LCID = 2057
However I never had to do this on the old set up and it seems like there is some setting that needs to change to fix all this on the new server (SQL and Web IIS 8) on same box.
The "Language" preferences on the machine are set to English (UK) although I wouldn't have though that would have made a difference.
If I run this code in a query analyser either RD into the box, or through my local SQL console connected to the machine over the network
select name ,alias, dateformat
from syslanguages
where langid =
(select value from master..sysconfigures
where comment = 'default language')
In a query window (Remote Desktop into server) I get back
Name Alias dateformat
British British English dmy
So it seems like something to do with the connection between the ASP website or .NET app and the server/database. Something I have missed or need to change as this all worked fine on the old WebServer -> Database Server setup we had.
Can you think of something I have overlooked or need to do to ensure dates are shown as UK on the website without editing every ASP page as I didn't have to do that on the old setup.
Thanks in advance for any help!
-
I just want to quickly add that putting
<%
'Set the server locale
Session.LCID = 2057
%>
At the top of the ASP pages does force the site to show the dates correctly as UK format e.g 03/08/2016 however I don't really want to add this to all the many pages that show dates as there is no global.asa file PLUS I never had to do this before so something is up that I need to change somewhere.
-
Come on this must be a standard issue many people have faced before - I am sure I used to come here years ago to find the answer.
Someone must have some clues?
-
If all the settings on the server and instance are correct, and the code runs fine from outside the app, then look to the app and the web server. There may be a few here who know and understand ASP and web apps, but I am not one of them and this is about as far as I can go.
-
So your saying that all my SQL settings ARE correct then?
I don't suppose you know any GOOD (as in expert) forums that would deal with sort of issue.
I am guessing whether it's ASP or ASP.NET it wouldn't make a difference. The machine is WIN 2012 IIS 8. So maybe there is a setting in that somewhere I don't know about.
Any ideas of GOOD forums to ask this question in let me know.
It's just a pain as it worked on Win 2003 / IIS 7 / SQL 2005 and then moved to a dedicated server and I get all these issues.
Not good!
-
I've been into IIS and changed the
.NETGlobalization - Culture and UI Culture to English (en). English (United Kingdom) en-gb
at both the server level and on the website levels as well.
That didn't seem to stop it and it makes me wonder what is going on because when dates are entered into a form the ASP code uses VBScript date functions to get the day, month and year and switch them around from dd/mm/yyyy format correctly into ISO format YYYY-MM-DD etc where they are saved in the DB like that.
So the website/server must know what part of the "string" a user enters is a day, month or year for it to do that.
However I found that by wrapping the dates in a CONVERT function e.g CONVERT(varchar, Racedate,103) I get a date stored in the DB as 2015-12-31 00:00:00.000 appearing on the webpage as 31-Dec-15 which is correct but the SQL is ordering the results by the TOP(20) ordered by Racedate therefore when I use this Convert statement the horse from 2015 is at the top of the list. When I remove the Convert statement I get a different horse - one from yesterday, date on website 08-Aug-16 and in the DB 2016-08-08 00:00:00.000.
If I use DATEPART(DAY,Racedate) functions the Day,Month and Year are all split out correctly.
I have also all tried this logged in with the user that the website uses to connect to the DB not my main sysadmin controller.
I just know that somewhere is a simple solution to all this as I've been through it all before, UK dates getting mixed up as US dates then errors later on when the day is over 12 as it treats it as a month.
That means I've got 3 days to sort this!
Help
-
Rob Reid-246754 (8/9/2016)
I've been into IIS and changed the.NETGlobalization - Culture and UI Culture to English (en). English (United Kingdom) en-gb
at both the server level and on the website levels as well.
That didn't seem to stop it and it makes me wonder what is going on because when dates are entered into a form the ASP code uses VBScript date functions to get the day, month and year and switch them around from dd/mm/yyyy format correctly into ISO format YYYY-MM-DD etc where they are saved in the DB like that.
So the website/server must know what part of the "string" a user enters is a day, month or year for it to do that.
However I found that by wrapping the dates in a CONVERT function e.g CONVERT(varchar, Racedate,103) I get a date stored in the DB as 2015-12-31 00:00:00.000 appearing on the webpage as 31-Dec-15 which is correct but the SQL is ordering the results by the TOP(20) ordered by Racedate therefore when I use this Convert statement the horse from 2015 is at the top of the list. When I remove the Convert statement I get a different horse - one from yesterday, date on website 08-Aug-16 and in the DB 2016-08-08 00:00:00.000.
If I use DATEPART(DAY,Racedate) functions the Day,Month and Year are all split out correctly.
I have also all tried this logged in with the user that the website uses to connect to the DB not my main sysadmin controller.
I just know that somewhere is a simple solution to all this as I've been through it all before, UK dates getting mixed up as US dates then errors later on when the day is over 12 as it treats it as a month.
That means I've got 3 days to sort this!
Help
if you are doing the ORDER BY in your query using the CONVERT(varchar, Racedate,103) then yes, you aren't going to get what you expect. You can use the CONVERT in the select list return the date in dd MMM yy format, but use the unconverted column Racedate in the ORDER BY to properly order the data.
Other than that, I have nothing to help you. I asked if any one else may have ideas to jump on this thread, but I guess no one else has any ideas either. You may want to try the MSDN forums for ASP and see what they have.
-
Yes as I said in the reply I only did the CONVERT on the SELECT column NOT the order by statement which is why I am so confused.
I had added
Session.LCID = 2057
To the top of the page so the locale is UK but why would JUST changing the format of the date in the SELECT part (NOT THE ORDER BY PART), change which results were shown on the page.
If I remove the CONVERT(varchar,RaceDate,103) as RaceDate from the SELECT statement and just use Racedate with no reformatting then I get the right results e.g a horse from yesterday is at the top of the page.
If I leave the CONVERT in the SELECT part - remember the ordering is JUST (with both selects) --> ORDER BY Racedate DESC
So I am not changing the ORDER BY in any way just the formatting of the SELECT column yet I get totally different horses at the top of the list e.g ones from 2015 not yesterday.
This is really making my head hurt.
I have posted on ASP / Stackoverflow boards but they don't tell me much I haven't done already apart from re-design my whole system and store dates as 6 columns - Year, Month, Day, Hour, Minute, Second etc which is a level of normalisation I don't think is necessary.
I am sure I solved issues like this before with just a change to the SQL User login to ensure it was English (UK) not English (US) and nothing more than that. I never had to add Session.LCID = 2057 at the top of pages that outputted or saved dates.
Also if I use my Kill page (clear all sessions, cookies, application data), then run code to output the default LCID, DateFormat and Currency I get this...
Default LCID is: 2057
Date format is: 09/08/2016
Currency format is: £350.00
Which seems correct. I am just dreading what will happen when it reaches the 13th of the month as some parts seem to be working others don't.
-
Using or not using the COVERT statement in the SELECT list should not change the sort order if you are not changing the ORDER BY clause.
The follow two SQL SELECT statement should return the same data in the same order:
select top (20)
rt.col1,
rt.col2,
convert(varchar(30), rt.Racedate, 103) RaceDate,
rt.col3
from
dbo.RaceTable rt
order by
rt.Racedate desc
select top (20)
rt.col1,
rt.col2,
rt.Racedate,
rt.col3
from
dbo.RaceTable rt
order by
rt.Racedate desc
-
I know they shouldn't that's why I am so confused to what is going on. I will try and insert some screen captures so you can see.
If you go here > https://www.fromthestables.com/race-history.asp you will see (unless someone has changed it since I last looked), that Captain Peacock from the 8th Aug 2016 is top of the list.
Now I change the SQL to this - to use the CONVERT statement JUST in the SELECT part.
SELECT TOP 40 HorseName, CourseName, TrainerName, CONVERT(varchar,RaceDate,103) as RaceDate, RaceTime, FinalOdds, FinishPosition, TrainerReview
FROMRACE_REVIEWS as r
JOINHORSES as h ONh.HorsePK = r.HorseFK
JOINCOURSES as c ONc.CoursePK = r.CourseFK
JOINTRAINERS as t ON t.TrainerPK = r.TrainerFk
WHERE Status = 'H' AND ShowOnResults = 1 AND PhoneOnly = 0
ORDER By RaceDate DESC;
And if I look at the page I get this...
As you can see the date at the top Tuesday, 09 August, 201ó is correct which just uses an ASP function to get the current Date() formatted as I want it.
However you can see that the horses have completely changed at the top of the list. The top horse being Ilzam from the 31st December 2015 and then Howaboutnever from the 31st December 2015.
I don't understand how this can be happening.
Unless something stupid has happened you are right the order by statement has NOT changed only the SELECT part. So the order of the horses when ordered by racedate DESC should not change just the format of the date shown.
Also you can see that the dates next to the horses ARE the same UK format (apart from using a dash - not sure where that is set as I prefer and the website USED to show the dates with slashes e.g 08/Aug/2016) - so I am guessing it's a webserver (maybe even a browser) issue - except my browser hasn't changed it's settings and I have used this site for years.
Weird or a sign of something?
-
Rob Reid-246754 (8/9/2016)
I know they shouldn't that's why I am so confused to what is going on. I will try and insert some screen captures so you can see.If you go here > https://www.fromthestables.com/race-history.asp you will see (unless someone has changed it since I last looked), that Captain Peacock from the 8th Aug 2016 is top of the list.
Now I change the SQL to this - to use the CONVERT statement JUST in the SELECT part.
SELECT TOP 40 HorseName, CourseName, TrainerName, CONVERT(varchar,RaceDate,103) as RaceDate, RaceTime, FinalOdds, FinishPosition, TrainerReview
FROMRACE_REVIEWS as r
JOINHORSES as h ONh.HorsePK = r.HorseFK
JOINCOURSES as c ONc.CoursePK = r.CourseFK
JOINTRAINERS as t ON t.TrainerPK = r.TrainerFk
WHERE Status = 'H' AND ShowOnResults = 1 AND PhoneOnly = 0
ORDER By RaceDate DESC;
And if I look at the page I get this...
As you can see the date at the top Tuesday, 09 August, 201ó is correct which just uses an ASP function to get the current Date() formatted as I want it.
However you can see that the horses have completely changed at the top of the list. The top horse being Ilzam from the 31st December 2015 and then Howaboutnever from the 31st December 2015.
I don't understand how this can be happening.
Unless something stupid has happened you are right the order by statement has NOT changed only the SELECT part. So the order of the horses when ordered by racedate DESC should not change just the format of the date shown.
Also you can see that the dates next to the horses ARE the same UK format (apart from using a dash - not sure where that is set as I prefer and the website USED to show the dates with slashes e.g 08/Aug/2016) - so I am guessing it's a webserver (maybe even a browser) issue - except my browser hasn't changed it's settings and I have used this site for years.
Weird or a sign of something?
Change this:
SELECT TOP 40 HorseName, CourseName, TrainerName, CONVERT(varchar,RaceDate,103) as RaceDate, RaceTime, FinalOdds, FinishPosition, TrainerReview
FROMRACE_REVIEWS as r
JOINHORSES as h ONh.HorsePK = r.HorseFK
JOINCOURSES as c ONc.CoursePK = r.CourseFK
JOINTRAINERS as t ON t.TrainerPK = r.TrainerFk
WHERE Status = 'H' AND ShowOnResults = 1 AND PhoneOnly = 0
ORDER By RaceDate DESC;
To this:
SELECT TOP 40 HorseName, CourseName, TrainerName, CONVERT(varchar,RaceDate,103) as RaceDate, RaceTime, FinalOdds, FinishPosition, TrainerReview
FROMRACE_REVIEWS as r
JOINHORSES as h ONh.HorsePK = r.HorseFK
JOINCOURSES as c ONc.CoursePK = r.CourseFK
JOINTRAINERS as t ON t.TrainerPK = r.TrainerFk
WHERE Status = 'H' AND ShowOnResults = 1 AND PhoneOnly = 0
ORDER By r.RaceDate DESC; -- <<< Change is here
-
Just an FYI, since you are joining four tables together you really should use the table aliases to identify which table each of your columns come from in the SELECT list. Do not rely on the fact that each column name in each of the tables happen to be unique. Plus, it helps others that may be reading the code to know which tables the various data is coming from without making assumptions or looking at the table schema.
-
Well done that fixed that results page but I don't understand why as I just checked all the tables in the SELECT statement and the ONLY one with a column called Racedate was in Race_Reviews so a prefix shouldn't have been necessary should it?
I'd like to know why that fixed that report if possible but it still doesn't fix the larger issue of ensuring my website is UK Formatted as it was before - without adding Session.LCID statements everywhere or having to format dates with CONVERT statements when I never needed to before.
But thanks.
-
Rob Reid-246754 (8/9/2016)
Well done that fixed that results page but I don't understand why as I just checked all the tables in the SELECT statement and the ONLY one with a column called Racedate was in Race_Reviews so a prefix shouldn't have been necessary should it?I'd like to know why that fixed that report if possible but it still doesn't fix the larger issue of ensuring my website is UK Formatted as it was before - without adding Session.LCID statements everywhere or having to format dates with CONVERT statements when I never needed to before.
But thanks.
The ORDER BY clause allows you to sort the results set based on the column alias. Since you aliased the RaceDate column with the same name it used the result of the conversion instead of the value from the database. This is one reason you should always use table aliases to clearly identify the columns from the tables.
I can help you with the SQL side, but I have no idea what to tell you about the web site (ASP) side of things.
-
Yes the ASP side is very tricky - not many ASP Classic coders left to ask!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply