Fun with Dates! Web Server, SQL Server, IIS and ASP Classic - Problems and Solutions

Fun with Dates! Web Server, SQL Server, IIS and ASP Classic - Problems and Solutions

By Strictly-Software

Dates can be a nightmare especially when moving servers.

A setup that ran perfectly on an old system can crumble up into a nightmare when the code and database is ported to a new server. I recently had this problem moving from a webserver connecting to database server to an all in one server based at the French hosting company OVH.

At first everything seemed okay then I started to notice the errors such as

1. Dates entered on a web page form as dd/mm/yyyy on submission coming back in US format e.g 22/08/2016 would come back as 8/22/2016 why there was no trailing zero I have no idea.

2. Primary Key / Index errors where the date was part of the key and it thought duplicates were being added into the system.

Good Practice

I always thought I ran good practice on my systems by doing the following but despite all these settings I was still getting US dates instead of UK dates shown on the website. However you should still do this as it limits the chances of error.

1. I ensure all Stored Procedures have SET DATEFORMAT YMD at the top and I store all dates as ISO format yyyy-mm-dd in the database.

2. All database logins used to pass information to the database are set to have "British English" as their "Default Language".

3. The database properties under options is set to British English.

4. The server properties under Advanced -> Default Language is set to British English.

5. On the website I always ask users and use client/server side validation to ensure they enter the dates as UK format dd/mm/yyyy.

6. I then convert that with a simple function into ISO format yyyy-mm-dd hh-mm-ss to pass to the stored procedure that saves the date. Having the SET DATEFORMAT YMD at the top of the stored procedure also helps to ensure SQL treats dates in that order Year - Month - Day etc.

I also always have on my site a Kill page which cleans out all session and application data e.g

<%
Application.Contents.RemoveAll()
Session.Contents.RemoveAll()
Session.Abandon()
%>

This is great if I need to wipe all stored info quickly especially as I have another page that gives me loads of Session, Application, Cookie and HTTP information. It also gives me much more including dates and times from the Web Server e.g NOW() and SQL Server e.g GETDATE() so I can check they are in sync.

I also show the default locale and currency formats. A simple version is below.

<%
response.write("<p>")
response.write("Default LCID is: " & Session.LCID & "<br>")
response.write("Date format is: " & date() & "<br>")
response.write("Currency format is: " & FormatCurrency(350))
%>

The LCID stands for the Server Locale and you can set it either in your Global.asa page if you use one or on the login page or in a global include that all pages use. English - United Kingdom has a value of 2057, the USA is 1033. You can read more about the locales here.

The result I got from my test page was

Default LCID is: 2057
Date format is: 09/08/2016
Currency format is: £350.00

This is all correct.

I have never had to resort to setting the Session.LCID on the website before and the Web Servers globalization settings were set the to the UK. This made it all the more stranger that I was getting US dates.

However I followed ALL of the steps below apart from the last step - which really is a last resort and it fixed the issue. It really is a shame that there isn't just one place where you can set your region and date formats that affects SQL and your Website but there just isn't.

Maybe a clever programmer could write something that would burrow away into all the servers settings and give you a report of what needs changing?

I already have a console .NET app that I run from a command prompt on any machine that tells me whether I can connect to the DB with specific ADO connection string params and LOCALHOST. It returns information about SQL logins, security such as access to extended stored procedures and any collation differences if there are any. It also shows me installed memory and disk space, used memory and disk space, all connected drives mapped drives and user logon history. Plus it attempts to send out an email using LOCALHOST as the mail relay using no ports and standard ports.

It also checks that it can access the outside world with a WebHTTP request to obtain the actual IP address of the machine before doing a PING to Google to test for speed. If I had the time I would probably love to delve into a project to solve the date issue as it's one that just keeps cropping up on new servers.

Debugging, Tests and Solutions

Apart from the good practices which I listed above and resorting to setting Session.LCID = 2057 at the top of your ASP pages there are some other things to try.

1. Test that your ISO dates are actually being stored as ISO NOT US e.g 2016-09-08 could be the 8th August in the UK (or ISO), or 9th of July if stored as US format. Do this with a simple SQL statement and login to your database using the connection string properties your website would not as an administrator.

This way you are using the properties of the users login and you can compare the results with you logged in as admin and if they are out of sync you should re-check your login properties again.

SELECT TOP(20) Racedate as ISODate,CONVERT(varchar, Racedate,103) as UKDate,datepart(day,Racedate) as DAYPart,datepart(month,Racedate) as MONTHPart,datepart(year,Racedate) as YEARPart
FROM   RACES
ORDER BY Racedate DESC

This should show you how the date is stored as a string (as all dates are really floating point numbers that would make no sense in a select if you saw them), as well as how the database sees each part of the ISO date.

So the DAYPart column should be the right section of the ISODate and left section of the UKDate and the month should be in the middle.

2. Test that your database is using a British format to return data to the client by running this SQL.

SELECT name, alias, UPPER(dateformat) as DateFormat
FROM syslanguages
WHERE langid =
 (SELECT value FROM master..sysconfigures
 WHERE comment = 'default language')

If everything is setup correctly you should get results like below:

Name  - Alias  - Dateformat
British - British English - DMY

If you don't get your desired country format back then the issue could be purely on the SQL Server / Database so go back over the good practices to ensure the Server/Database and Logins all have British English (en-gb) as their Default Languages / Locales in any setting you can see.

If you know that dates were entered for today and they look like ISO Date format in the tables then run a simple SELECT with a DATEDIFF(DAY,Stamp,GETDATE())=0 clause to see if they are returned.

If they are then you know the dates are being stored in the DB correctly so the issue is probably due to the web server.

3. Some people say that you should store your dates in 6 columns, Year, Month, Day, Hour, Minute, Second but personally I don't think this level of normalization is necessary OR should be.

However if you only have a couple of places to change then it might offer a solution. However if you already have a big database with lots of dates being shown and entered it would be a lot of work to normalize like this.

4. Go into your web servers Control Panel and select Language. Then ensure all possible date formats, languages and locales are set to use your desired location e.g chose UK instead of US and ensure the date formats are dd/mm/yyyy not mm/dd/yyyy.

There should be an "Advanced" link on the left where you can set the order of preference related to languages there as well. You will need to restart the machine for these to take affect.

5. Follow these steps from your Control Panel so that all users get to use your own regional settings. It's just one more place regions and formats are set which should be looked at if you are having an issue.

• Go to Control Panel.
• Click Region and you will see a screen with 3 tabs (Formats, Location and Administrative).
• Click Formats and choose the settings you prefer.
• Click Additional settings.
• Click Date tab.
• Change Short date to desired format and confirm dialog. 
• Click Location and choose the settings you prefer.
• Click Administrative tab. 
• For "Welcome screen and new user accounts", click copy settings. 
• From the new window, click both checkboxes for "welcome screen and system accounts" and "new user accounts" (if you skip this step, you will still see the issue because IIS uses system account).
• Approve all changes for Region by clicking OK on all open windows.
• Open Command prompt, write iisreset and enter.
• If you still don't see the changes try logoff and logon.
• Or reboot.

6. Go into IIS and at Server level and Database level go into the .NET Globalization settings and change the options Culture and UI Culture to English (en). Even if you are using ASP classic it is still worth doing this.

7. Compare the webpages showing up the US dates in different browsers, FireFox, Chrome and IE for example. If there is a difference then it could be down to your browsers locale settings. You may have had an anti virus checker run and reset some browser properties without you knowing so it's worth a shot especially if you know that not everyone is seeing the same values as yourself.

8. If it really comes down to it and you cannot resolve the issue then you could wrap all your dates from any SQL returned to your web pages in a function that uses VBScript to split the day, month and year apart before putting them back together in the format you want.

An example is below. You can use the built in functions Day(val), Month(val), or Year(val) or DatePart("d",val) to get the part of the date out. This function also uses a quick easy way to append zeros onto single character numbers 1-9 become 01 or 09.

You will also see by this method whether or not the ASP code manages to select the correct part of the date out of the SQL column returned to you.

For example if you have a date of 12/08/2016 (where 12 is the day), and you use Datepart("d",dtDate), where dtDate is the variable holding 12/08/2016. Then you will see if you get back the correct value of 12 (UK) or 08 (US). If you get an issue like this then check all your web server settings.

Function CorrectDate(dtDate)
 Dim dDay, dMonth, dYear
 dDay = Datepart("d",dtDate)
 dMonth = Datepart("m",dtDate)
 dYear = Datepart("yyyy",dtDate)

 CorrectDate = QuickNoFormat(dDay) & "-" & QuickNoFormat(dMonth) & "-" & dYear
End Function



Function QuickNoFormat(intNo) 
 '* If the number is only 1 character long add a zero to the front e.g 8 becomes 08
 If IsNumeric(intNo) Then
  QuickNoFormat = Right(Cstr(Cint(intNo) + 100),2)
 Else
  QuickNoFormat = intNo
 End If
End Function

Hopefully by following the good practice guide you shouldn't run into problems but if you do the list of solutions should help you out. It did with my latest Windows 2012 server.

Let me know if this is of any help for you.


By Strictly-Software

© 2016 Strictly-Software