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

Cool Javascript regular expressions

Using Lambda Functions for HTML Parsing

One of the cool features that made me scratch my head when I first came across it but now love to bits about Javascript is the ability to use lambda expressions. A lambda expression basically means that you can use a function as the argument for another function. This is best seen with the replace method where you can use a function as the replacement value for a matching string test e.g

somevar = something.replace(/pattern/, function(match, submatch){
if(/another pattern/.test(submatch)){
return match;
}else{
return "";
}
});

One of the ways that I have found to use this feature is within my WYSIWYG widget to parse user generated HTML content and to strip out any HTML tags or attributes that are not allowed to be entered.

The function starts off with a regular expression that matches all HTML tags and also provides a grouping that returns the actual HTML tag name.

theHTML = theHTML.replace(/<[/]?([^> ]+)[^>]*>/g, function(match,HTMLTag)

I can then use a function as my replacement value that will either return an empty string and remove the whole tag if its not allowed or otherwise run another replacement to handle attributes.

match = match.replace(/ ([^=]+)="[^"]*"/g, function(match2, attributeName)

This function does a similar job of replacing the attribute with an empty string if its not allowed or otherwise returning the sub group that matches the attribute/value pair. This ends up being a very cool way of parsing HTML content using the power of regular expressions.

The whole function is below:

// Set up my regular expressions that will match the HTML tags and attributes that I want to allow
var reAllowedAttributes = /^(face|size|style|dir|color|id|class|alignment|align|valign|rowspan|colspan|width|height|background|cellspacing|cellpadding|border|href|src|target|alt|title)$/i
var reAllowedHTMLTags = /^(h1|h2|a|img|b|em|li|ol|p|pre|strong|ul|font|span|div|u|sub|sup|table|tbody|blockquote|tr|td)$/i

function ParseHTML(theHTML){
// Start of with a test to match all HTML tags and a group for the tag name which we pass in as an extra parameter
theHTML = theHTML.replace(/<[/]?([^> ]+)[^>]*>/g, function(match,HTMLTag)
{
// if the HTML tag does not match our list of allowed tags return empty string which will be used as a
// a replacement for the pattern in our inital test.
if(!reAllowedHTMLTags.test(HTMLTag)){
return "";
}else{
// The HTML tag is allowed so check attributes with the tag

// Certain attributes are allowed so we do another replace statement looking for attributes and using another
// function for the replacement value.
match = match.replace(/ ([^=]+)="[^"]*"/g, function(match2, attributeName)
{
// If the attribute matches our list of allowed attributes we return the whole match string
// so we replace our match with itself basically allowing the attribute.
if(reAllowedAttributes.test(attributeName)){
return match2;
}else{
return "";  // not allowed so return blank string to wipe out the attribute value pair
}
});

}
return match;

}); //end of the first replace

//return our cleaned HTML
return theHTML;
}

Another good thing about this feature is that as well as being able to pass the match string in to the replacement function as a parameter you can also pass in any number of sub groups as extra parameters. So using my parseHTML function as an example again instead of only capturing the attribute name in my check for valid attributes I could also capture the attribute value and then pass that as an extra parameter to my replacement function like so:

match = match.replace(/ ([^=]+)="([^"]*)"/g, function(match2, attributeName, attributeValue)

So you could test for the validity of the supplied values if you wanted to. Maybe if you were allowing the class attribute you would want to check to make sure only certain class names were used.

This is brilliant for use in client side widgets and also as server side code for parsing user supplied HTML content. Remember even if you are using crusty ASP classic and writing your code in VB Script which has a really poor Regular Expression engine compared to Javascript you can still make use of this cool feature as there is nothing stopping you mixing and matching VB Script and Javascript on the server.