Base solution for your next web application
Open Closed

Return an ISO DateTime(Not Local) from the DB #10250

User avatar
0
compassinformatics17 created
  • What is your product version?: 4.8.1
  • What is your product type: MVC
  • What is product framework type: .NET Framework (Non Core)

Issue

  • Dates are stored in the SqlServer in ISO format YYYY-MM-DD HH:MM:SS
  • But query results return the dates pre-localised and not in ISO format as one would expect. This is not at all ideal since I would like to use the ISO date as a starting point for date transformations on the client side.
  • I have tried many approaches to get the framework to return the date in raw ISO form but none work, the results always seem to be pre-localised in the debugger and also in the JSON response.
  • I have also put this question on StackOverfllow last year: https://stackoverflow.com/questions/62016070/asp-net-abp-return-an-iso-datetimenot-local-from-the-db
  • Is there a way to make the framework default to ISO date time formatting rather than localising all results.

Things I have tried

  • Setting the DateTimeKind
  • Disabling DateTime Normalisation
  • Setting JSON parameters in Global.asax Application_Start() such as:
var json = GlobalConfiguration.Configuration.Formatters.JsonFormatter;
json.SerializerSettings.DateFormatHandling = DateFormatHandling.IsoDateFormat;
  • Commenting out "Clock.Provider = ClockProviders.Utc" in Global.asax.
  • Forcibly converting to ISO format using date.ToString('yyyy-MM-dd').
    • Cannot do this because the LINQ query is not aware of ASP date functions since comparable functions do not exist in SQL and hence the query will fail and throw and error.

Example of the issue

var query = _someRepository.GetAll();

var rows = query.Select(a => new RowViewModel { Id = a.Id, Name = a.Name, CreatedBy = a.CreatorUser.Name + " " + a.CreatorUser.Surname, CreatedOn = a.CreationTime //Returns 22/05/2020 10:05:05, I want 2020-05-22 10:05:05 });

Go to accepted answer
1 Answer(s)
  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @compassinformatics17

    When you use DisableDateTimeNormalization attribute, AspNet Zero doesn't get involved in date time formatting. So, in order to achieve what you want, you need to change ASP.NET's date format. Something like below should work;

    protected void Application_BeginRequest(Object sender, EventArgs e)
{
    var settingManager = AbpBootstrapper.IocManager.Resolve<ISettingManager>();
    var defaultLanguage = settingManager.GetSettingValue(LocalizationSettingNames.DefaultLanguage);

    CultureInfo ci = new CultureInfo(defaultLanguage);
    ci.DateTimeFormat.SetAllDateTimePatterns(
        new string[] { "dd/MM/yyyy" },
        'd'
    );
    
    System.Threading.Thread.CurrentThread.CurrentCulture = ci;
    System.Threading.Thread.CurrentThread.CurrentUICulture = ci;
}
Assignee
No one
Labels
Non yet