Entering SQL Records With Dates Uses Wrong Date Format

Question

Using: Entity Framework, Sql Server 2014. My application locale/globalisation is not set (yet).

My problem is this; When dealing with dates all around the application they are in the correct UK date format:- dd/mm/yyyy

However, when I use those dates and enter them in to my SQL database (on a shared hosted SQL Server), they go in in the US format mm/dd/yyyy.

How can I change it so that the dates go in to the database in the UK format? Bear in mind I can't touch the settings on the server.

I'm thinking through the connection string with a locale setting or in web.config, but I can't find anything definitive online that can tell me for sure.

I was thinking that this would sort it out but haven't tried it yet.

<configuration>
  <system.web>
    <globalization
      fileEncoding="utf-8" 
      requestEncoding="utf-8" 
      responseEncoding="utf-8"
      culture="en-GB"
      uiCulture="en-GB"
    />
  </system.web>
</configuration>

EDIT: This is the code generated by EF

  public int CreatedBy { get; set; }
    public System.DateTime CreatedDate { get; set; }
    public Nullable<int> ModifiedBy { get; set; }
    public Nullable<System.DateTime> ModifiedDate { get; set; }
    public bool Enabled { get; set; }
    public Nullable<int> ProductSettingId { get; set; }
    public Nullable<int> ProductAreaMinimumPremiumId { get; set; }
    public int ProductId { get; set; }
    public int StatusId { get; set; }
    public Nullable<System.DateTime> InceptionDate { get; set; }
    public Nullable<decimal> Gross { get; set; }
    public Nullable<decimal> Ipt { get; set; }

I'm having issues with all the DateTime properties.

If the date in the UI/app is 12/01/2017 it will go in the DB as 1st Dec 2017 whereas it should be 12th Jan 2017.

this is local web application configuration -- you need to change the database server. — Hogan, Jan 30 '17 at 15:43
Are you saving it as .net DateTime object, or you just using string? — j.v., Jan 30 '17 at 15:45
@j.v. It's a DateTime variable in EF. datetime in the database. — Kevin Dark, Jan 30 '17 at 15:47
I doubt this... There was a [somehow related question](http://stackoverflow.com/q/41888627/5089204) shortly. But in this case there was a self-done generic procedure in the middle which converted everything to strings. **The string you see is not the actual value!!** It is a formatted textual representation of the value. `.Net-DateTime` is mapped to `sql-DATETIME` in a typesafe way. There should not be any intermediate status you should have to bother about... — Shnugo, Jan 30 '17 at 15:54
The code you show after the last edit does not show, how you actual communicate your data with the RDBMS. Did you have a look at the link I posted. There were also nullable properties involved... — Shnugo, Jan 30 '17 at 16:27
@Shnugo I'm not sure I understand what information you're asking me for. I'm using Entity Framework to connect to a SQL 2005 DB (Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)) The fields in the DB are DateTime and EF is translating them to code as System.DateTime — Kevin Dark, Jan 30 '17 at 16:39

score 1 · Answer 1 · answered Jan 30 '17 at 17:08

I'm must apologise everyone! It turns out my issue was with the application culture/locale.

By adding:

 <configuration>
  <system.web>
    <globalization
      culture="en-GB"
      uiCulture="en-GB"
    />
  </system.web>
</configuration>

That worked.

I did run this line in on my DB as well, first so I don't know if both these together sorted it out.

  ALTER LOGIN {{MY LOGIN}} WITH DEFAULT_LANGUAGE=British

Entering SQL Records With Dates Uses Wrong Date Format

1 Answers1