locked
The Date/Time column of mvc webgrid loses "PM" when choose French language, why? RRS feed

  • Question

  • User-1651604128 posted

    My bilingual mvc web app uses webgrid to display Date/Time data from a table, here is the code:

     gridChangeLog.Column("Invoice_Date", "Date/Time", format: item => ((item.Invoice_Date == null) ? "" : item.Invoice_Date.ToString("yyyy-MM-dd h:mm:ss tt"))),

    This works fine, it shows: "2004-11-04 2:50:30 PM" in English,

    but it shows : "2004-11-04 2:50:30"  when I choose French language

    I think it may be related to my local language set up, is there anyway to make the "PM" showing when I choose French language, basically, to make it shows the same regardless I choose English or French?

    Thanks

    Friday, August 16, 2019 2:10 PM

All replies

  • User475983607 posted

    Peter Cong

    I think it may be related to my local language set up, is there anyway to make the "PM" showing when I choose French language, basically, to make it shows the same regardless I choose English or French?

    Please take the time to read the DateTime format documentation.  Then pick a format, and perhaps culture, that fits your UI requirements.

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

    Friday, August 16, 2019 2:30 PM
  • User753101303 posted

    Hi,

    French is not using the AM/PM indicator but a 0 to 23 range for hours (ie 2:50 PM is shown as 14:50). Your intent is unclear as it seems you want to show the same value so what is the point of allowing to choose French?

    Or you could change the  UICulture to show a French localized site and keep the Culture to "English" to keep showing numbers and dates the same way.

    When  I want to support multiple cultures I favor using standard formats shown at https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings rather than hardcoding a custom format that anyway can only support a "compatible" culture.

    Friday, August 16, 2019 6:09 PM
  • User-1651604128 posted

    Hi,

    French is not using the AM/PM indicator but a 0 to 23 range for hours (ie 2:50 PM is shown as 14:50). Your intent is unclear as it seems you want to show the same value so what is the point of allowing to choose French?

    Or you could change the  UICulture to show a French localized site and keep the Culture to "English" to keep showing numbers and dates the same way.

    When  I want to support multiple cultures I favor using standard formats shown at https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings rather than hardcoding a custom format that anyway can only support a "compatible" culture.

    The Date/time value is showing as one column of webgrid, when user highlights the cursor on one row and click, I want a popup window to show all related data selected and show the details on the popup dialog window. In order to show the selected row data, I need to choose 5 columns of data as a key and fetch the data from a table, and the date/time column is one of the column of the key, because the date/time of this column is saved in SQL table has "PM" in there, so If this "PM" is missing, then I can not get the value, that is why I need it  in order to get the real data saved in database table.

    Basically, regardless user select English or French, I want that date/time column display the real data which has "PM" there, Your this comment seems what I wanted,

    "Or you could change the  UICulture to show a French localized site and keep the Culture to "English" to keep showing numbers and dates the same way."

    Any insight how to implement this?

    Thanks a lot again,

    Friday, August 16, 2019 11:42 PM
  • User475983607 posted

    Peter, if you are using a DateTime type then you - the programmer - control how the DateTime is converted to a string.  The links in my first post illustrate how to format the DateTime in C#.  Since you are unable to perform this very simple task, I assume there is something else going on with your code.

    Share a code sample that reproduces this issue.  

    Saturday, August 17, 2019 11:23 AM
  • User753101303 posted

    Hi,

    Peter Cong

    the date/time of this column is saved in SQL table has "PM" in there,

    Seems a common misunderstanding. Which db are you using ? Is this a true date/time column ? A true date/time column doesn't have any particular format. You have to make the difference between how a value is actually stored in a db and how it is shown the problem for a date/time (but even at a lesser degree for a decimal value) being that each country can have its own way to show the SAME stored value.
    It's common for developer tools (such as SQL Server Management Studio) to show the value using its own fixed convention to avoid confusion exactly as in C# you express a value the same way regardless of any country convention.

    Still if you have var a=1234.67;  in, your C# code, it could be rendered as 1,234.67 or 1 234,67 etc... and likely tons of other conventions depending on the country...

    Assuming ASP.NET 4.x (else post rather in the ASP.NET Core forum) you can use https://support.microsoft.com/en-us/help/306162/how-to-set-current-culture-programmatically-in-an-asp-net-application to define the culture (either in the web.config or programmatically depending on the request).

    The CurrentUICulture is the one used to fetch resource files so that you can show a localized UI ("Welcome" or "Bonjour"). Seems you want to have "en" or "fr"

    The CurrentCulture is to control how conversions are done between the "native" value to and from string values according to a given country convention. Seems you always want to use "en" for this one ? (so it could be configured once for all in your web.config file).

    Or take the read pill and really switch fully from en to fr including showing date values according to the usual country convention ?

    Edit: It gave me the idea to try the following code that shows how this date could be written depending on the culture (showing each possible value and how many neutral cultures are using this convention). Basically the same principle you are used to for C# is true as well for a database ie a "lliteral value" is written (and stored in memory) the same way for everyone in C# but could be shown differently depending on each country convention.

    using System;
    using System.Linq;
    
    namespace ConsoleApp1
    {
        class Program
        {
            static void Main(string[] args)
            {
                // A single value
                var value = new DateTime(2004, 11, 4, 14, 50, 30);
                // Begin shown using multiple conventions
                var query = System.Globalization.CultureInfo.GetCultures(System.Globalization.CultureTypes.NeutralCultures)
                    .Select(o => value.ToString(o))
                    .GroupBy(o => o)
                    .Select(o=>new { o.Key,Count=o.Count()})
                    .OrderByDescending(item => item.Count);
                foreach (var item in query)
                {
                    Console.WriteLine("{0} ({1})", item.Key, item.Count);
                }
            }
        }
    }

    Saturday, August 17, 2019 2:24 PM
  • User-1651604128 posted

    mgebhard

    Peter, if you are using a DateTime type then you - the programmer - control how the DateTime is converted to a string.  The links in my first post illustrate how to format the DateTime in C#.  Since you are unable to perform this very simple task, I assume there is something else going on with your code.

    Share a code sample that reproduces this issue.  

    Hi mgebhard, sorry, I will study the two posts you provided tomorrow and hopefully I can find solution from there, I will let you know then, thanks

    Peter

    Sunday, August 18, 2019 3:30 PM
  • User-1651604128 posted

    Peter Cong

    I think it may be related to my local language set up, is there anyway to make the "PM" showing when I choose French language, basically, to make it shows the same regardless I choose English or French?

    Please take the time to read the DateTime format documentation.  Then pick a format, and perhaps culture, that fits your UI requirements.

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

    Hi mgebhard,

    I tried the URL links you provided, this format is working for both English and French Language, actually, it is regardless the language selection.

    DateTime date1 = new DateTime(2008, 4, 10, 6, 30, 0);
    Console.WriteLine(date1.ToString("s"));
    // Displays 2008-04-10T06:30:00                       

    But the only thing it that there is a "T" in the data display of webgrid column, so user may confuse and ask why there is "T" in the date string.

    Is that any way to get rid of that "T" for display only? I mean it needs that full string (includes that "T") to pass to controller for fetch data record from SQL db table, so ideally, in razor view, it shows without "T", and when passing back to controller, includes that "T"?

     

    Monday, August 19, 2019 1:43 PM
  • User-1651604128 posted

    Hi,

    Peter Cong

    the date/time of this column is saved in SQL table has "PM" in there,

    Seems a common misunderstanding. Which db are you using ? Is this a true date/time column ? A true date/time column doesn't have any particular format. You have to make the difference between how a value is actually stored in a db and how it is shown the problem for a date/time (but even at a lesser degree for a decimal value) being that each country can have its own way to show the SAME stored value.
    It's common for developer tools (such as SQL Server Management Studio) to show the value using its own fixed convention to avoid confusion exactly as in C# you express a value the same way regardless of any country convention.

    Still if you have var a=1234.67;  in, your C# code, it could be rendered as 1,234.67 or 1 234,67 etc... and likely tons of other conventions depending on the country...

    Assuming ASP.NET 4.x (else post rather in the ASP.NET Core forum) you can use https://support.microsoft.com/en-us/help/306162/how-to-set-current-culture-programmatically-in-an-asp-net-application to define the culture (either in the web.config or programmatically depending on the request).

    The CurrentUICulture is the one used to fetch resource files so that you can show a localized UI ("Welcome" or "Bonjour"). Seems you want to have "en" or "fr"

    The CurrentCulture is to control how conversions are done between the "native" value to and from string values according to a given country convention. Seems you always want to use "en" for this one ? (so it could be configured once for all in your web.config file).

    Or take the read pill and really switch fully from en to fr including showing date values according to the usual country convention ?

    Edit: It gave me the idea to try the following code that shows how this date could be written depending on the culture (showing each possible value and how many neutral cultures are using this convention). Basically the same principle you are used to for C# is true as well for a database ie a "lliteral value" is written (and stored in memory) the same way for everyone in C# but could be shown differently depending on each country convention.

    using System;
    using System.Linq;
    
    namespace ConsoleApp1
    {
        class Program
        {
            static void Main(string[] args)
            {
                // A single value
                var value = new DateTime(2004, 11, 4, 14, 50, 30);
                // Begin shown using multiple conventions
                var query = System.Globalization.CultureInfo.GetCultures(System.Globalization.CultureTypes.NeutralCultures)
                    .Select(o => value.ToString(o))
                    .GroupBy(o => o)
                    .Select(o=>new { o.Key,Count=o.Count()})
                    .OrderByDescending(item => item.Count);
                foreach (var item in query)
                {
                    Console.WriteLine("{0} ({1})", item.Key, item.Count);
                }
            }
        }
    }

    Hi PatriceSc,

    Sorry for the confusing, it is my mistakes, I checked the SQL server database table again,  now let me correct it.

    Yes, you are right, The database is SQL server database 2008, the Data Type is datetime, the Invoice_date column in SQL server database table is saved as this

    format: 2014-11-04 14:50:30.000, there is no "PM" saved in SQL table.

    In the webgrid column of razor view, this data column formatted as : Invoice_Date.ToString("yyyy-MM-dd h:mm:ss tt"), so it displays: 2014-11-04 2:50:30 PM, this works as I expected, I can use this data to retrieve the database table and get the correct result. so in other words, the "2014-11-04 2:50:30 PM" equals to "2014-11-04 14:50:30.000,"

    But if I switched to French version, this same data column displays "2014-11-04 2:50:30 ", the "PM" is not showing there any more, so when this data is passing to controller the data is not fetched, because in this case,  the "2014-11-04 2:50:30 " does not equal to "2014-11-04 2:50:30 " because the "PM" is missing.

    I also tried this format Invoice_Date.ToString(''s'), it shows data as this format in webgrid: "2014-11-04T14:50:30 ", this format does not change regardless I switch English and French,

    and it is fetched the correct data from SQL database, but the only thing is that there is a "T" in the data, so it may cause confusing by users. is that any way to get rid of "T" there?

    thanks again for your help,

    Monday, August 19, 2019 2:26 PM
  • User475983607 posted

    Peter, you are confusing a DateTime type and a string that represents a date. 

    You have total control over converting a DateTime type to a string.  The documentation in my first post has everything you need to format a DateTime type any way you like using C#. 

    Here are the docs, if you need to do the conversion in T-SQL

    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    JavaScript docs

    https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date

    Monday, August 19, 2019 5:59 PM