title: Fun with US and UK dates on a new server
body: <p>I have just had the task of moving an old ASP website / SQL DB to a new dedicated OVH server (French - all defaults set to US-Eng).</p>
  <p>The DB has moved from SQL 2005 to SQL 2012 (web edition 64 bit).</p>
  <p>I am having the old issue of date formats showing up as US format on the website e.g <code>8/3/2016</code> instead of <code>03/08/2016</code>. This is even though in the database they are stored as ISO Date <code>2016-08-03</code> etc.</p>
  <p>I enter the dates on the ASP Classic, website as UK format
  e.g. <code>03/08/2016</code> and convert them to ISO format <code>2016-08-03</code> in the SQL that is passed to the Stored Procedure that has SET DATEFORMAT YMD at the top of it.</p>
  <p>If I check the tables in the DB they are all stored correctly as ISO dates.</p>
  <p>I have made sure all the SQL Logins to the DB have &quot;British English&quot; selected as their &quot;Default Language&quot;.</p>
  <p>If I view the database properties under options the Default Language is British English.</p>
  <p>If I view the server properties under General-&gt;Language it's English (United States) but under Advanced-&gt;Default Language it's British English.</p>
  <p>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 of US dates I don't know).</p>
  <p>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 on this box.</p>
  <p>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</p>
  <pre><code>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';
  </code></pre>
  <blockquote>
  <p>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).</p>
  </blockquote>
  <p>However if I copy that EXEC statement and run it direct in a query analyser window it runs WITHOUT ANY ERROR.</p>
  <p>I have been searching the web 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 &gt; <a href="https://www.webwiz.co.uk/kb/asp-tutorials/date-time-settings.htm" rel="nofollow noreferrer">https://www.webwiz.co.uk/kb/asp-tutorials/date-time-settings.htm</a></p>
  <pre><code>'* Set the server locale to UK 
  Session.LCID = 2057
  </code></pre>
  <p>This has worked on SOME pages but I have never had to do this before.</p>
  <p>On pages with long lists of records and dates in one column I have wrapped the date in CONVERT(varchar, GETDATE(),103) in the SQL and it returns correctly on the page.</p>
  <p>I am confused though as I have never had to do this on the old set up and it seems like there must be some setting that needs to change to fix all this on the new server (SQL and Web IIS 8) on same box.</p>
  <p>I have tried going into the .NET Globalization for the site and changing Culture and UI Culture to English (en). However that didn't fix everything.</p>
  <p>The &quot;Language&quot; preferences on the machine are set to English (UK) although I wouldn't have though that would have made a difference.</p>
  <p>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</p>
  <pre><code>select name ,alias, dateformat
  from syslanguages
  where langid =
  (select value from master..sysconfigures
  where comment = 'default language')
  </code></pre>
  <p>In a query window (Remote Desktop into server) I get back</p>
  <pre><code>Name Alias dateformat
  British British English dmy
  </code></pre>
  <h2 id="question">Question</h2>
  <p>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 -&gt; Database Server setup we had.</p>
  <p>Is there something I have overlooked to ensure dates are shown as UK on the website without editing every ASP page and SQL that contains dates as I never had to do that for the old setup.</p>