none
How to change date format in Linq query RRS feed

  • Question

  • Hi All,

    I'm trying to query a MS SQL 2016 db table using the following linq expression.

    return _context.RVOPlantReadings
                                  .Where(r => r.ReadingTimeStamp.Year.Equals(readingDate.Year)
                                       && r.ReadingTimeStamp.Month.Equals(readingDate.Month)
                                       && r.ReadingTimeStamp.Day.Equals(readingDate.Day)
                                       && r.PlantName.Equals(systemName) && r.companyId.Equals(companyID))
                                   .FirstOrDefault();

    readingDate is passed into the query in the format dd-MM-yyyy
    However the database is configured to use en-US format so when I pass in the date 04-01-2016 (fourth of Jan 2016), the query returns the entry with the ReadingTimeStamp of 01-04-2016 (first of April 2016). I can't format the date using formatting such as readingDate.toString("MM-dd-yyyy") to try and turn the date I'm passing in to US format as this will throw an error.
    readingDate is a DateTime object.
    Running 'dbcc useroptions' on the SQL server returns
    language = us_english
    dateformat = mdy

    When I asked the provider (Smarter ASP.NET) to change the language/date format on the server they replied

    Dear Customer,

    We providing hosting service all over the world, we cant change the shared server global setting. It is use US format by default. 

    For the database data, it is always used for your web applications, so update your application's web.config file would control the data time format. 

    Adding a <globalization> node to the web.config file obviously has no effect at all. Dates shown on razor pages can be / are formatted to en-GB format.

    Can anyone suggest anything please.

    Tuesday, July 24, 2018 7:28 AM

Answers

  • Stefan, Just wanted to say I solved the problem by doing things a different way and not trying to use a date. Very many thanks for your help.
    • Marked as answer by Kiboko2 Tuesday, July 24, 2018 12:04 PM
    Tuesday, July 24, 2018 12:04 PM

All replies

  • hmm, the format does not matter, when you use native data types. Thus the first thing is to check the data types used in your tables (DATE, TIME, DATETIME, DATETIME2, DATETIMEOFFSET). Check carefully also your views and procedures, not that you cast them to [N]VARCHAR. This the thing you need to avoid at all costs.

    Then your Linq query should work, when ReadingTimeStamp and readingDate are DateTime in .NET:

    return _context.RVOPlantReadings.Where(
    	r => r.ReadingTimeStamp.Equals(readingDate) && 
    		r.PlantName.Equals(systemName) && 
    		r.companyId.Equals(companyID)
    	).FirstOrDefault();

    Then you should use proper internationalization when converting strings to dates or numbers or vice versa by using IFormatProvider. See also How to: Define and Use Custom Numeric Format Providers.

    Tuesday, July 24, 2018 7:52 AM
  • Thank you so much Stefan for your response.

    The data type for the ReadingTimeStamp is datetime2 with a length of 8. The table RVOPlantReadings has a number of foreign keys into other tables and the purpose of the query is to get the index into one of the other tables so I can retrieve the readings in that table. So the ReadingTimeStamp of 04-01-2016 (fourth of Jan 2016) has the index of 8 in the foreign key table but the ReadingTimeStamp of 01-04-2016 returns the index of 83. Thanks for your advice regarding converting strings to dates and vice versa, I don't seem to have any issues displaying the correct dates in the format I want to, my problem is that I cannot get the correct RVOPlantReading entry back from my Linq

    Tuesday, July 24, 2018 8:45 AM
  • Sorry, I forgot to mention that the ReadingTimeStamp is a DateTime value so using the revised Linq that you kindly provided does not work since the readingDate that is passed in has a time stamp of 00:00:00  and the ReadingTimeStamp has a time value of 07:00:00. I have no way of knowing the ReadingTimeStamp time value before I make the query, so the query fails since there is no record with a ReadingTimeStamp of 04/01/2016 00:00:00, only one with a ReadingTimeStamp of 04/01/2016 07:00:00

    regards

    Tuesday, July 24, 2018 8:57 AM
  • Here is the sequence of calls and the code I am using if that is any help

    //the call that is made when a reading date is selected so that it can be edited
    public void OnGetEdit(DateTime editDate, string systemName, int companyID)
            {
                //get the reading
                ROPlantReadings reading = ReadingRepo.GetReadingID(editDate, systemName, companyID);
                PressureReadingID = reading.PressuresReadingID; //this is an int32
                ReadingDate = editDate.ToString("dd-MM-yyyy"); //this is held on the page in a hidden field as a string
                SystemName = systemName; //string
                CompanyID = companyID;      //int32     

                RVOPressuresReading pressureReading = ReadingRepo.GetPressureReading(PressureReadingID);
            }

    //in IReadingRepository.cs

    //get the reading ID to edit any reading
            ROPlantReadings GetReadingID(DateTime readingDate, string systemName, int companyID);
    //for editing a pressure reading        
            RVOPressuresReading GetPressureReading(int readingID);


    //in ReadingRepository.cs

    //this is to get the id for editing a reading
            public ROPlantReadings GetReadingID(DateTime readingDate, string systemName, int companyID)
            {           
                   return _context.RVOPlantReadings
                           .Where(r => r.ReadingTimeStamp.Year.Equals(readingDate.Year)
                                && r.ReadingTimeStamp.Month.Equals(readingDate.Month)
                                && r.ReadingTimeStamp.Day.Equals(readingDate.Day)
                                && r.PlantName.Equals(systemName) 
       && r.companyId.Equals(companyID))
                            .FirstOrDefault();
            }

    //these are for editing a pressure reading        
            public RVOPressuresReading GetPressureReading(int readingID)
            {
                return _context.RVOPressuresReading
                    .Where(f => f.ID.Equals(readingID))
                    .FirstOrDefault();
            }

    Tuesday, July 24, 2018 9:17 AM
  • So, question first: Why do you compare by date only, when you have a time portion?

    But this means, that you need to compare a date/time range or a date only. Using date only is easier:

    return _context.RVOPlantReadings.Where(
        r => r.ReadingTimeStamp.Date.Equals(readingDate.Date) &&
            r.PlantName.Equals(systemName) &&
            r.companyId.Equals(companyID)
        ).FirstOrDefault();

    Tuesday, July 24, 2018 10:19 AM
  • Hi Stefan,

    I compare by date only because I don't know the time the reading was taken. I am showing a list of say 31 readings taken on each day of the month. I have to record the time the reading was taken for other reasons (to make sure the reading was taken early in the day for one reason). So for this exercise, every day an operative has to record some readings and upload them to the db using a mobile phone app. Then later, someone will want to review the readings and possibly edit them if it is apparent that the operator entered an incorrect reading say entered 4 instead of 0.4. When I display this list of 31 readings on the web page I only show the date e.g 01-01-2016, 02-01-2016, 03-01-2016 etc. The user can select the date range to display so I am not going to the table to show the dates for a specific period. I may have to show 300 days of readings (this will appear in an HTML table with 300 rows). The user selects the date range and I return the dates as below. I know the start date and the end date when I draw the table but not the times of the readings. They can only be known once I have been to the table. That is why i was trying to match year, month and day in my original Linq

    Tuesday, July 24, 2018 10:39 AM
  • here is the code used to display the table

     @{ int i = 0;}
                    @if(Model.PressureReadings.Count > 0)
                    {
                        foreach (var item in Model.PressureReadings)
                        {
                            <tr>
                                <td style="font-size:x-small;"><a title="Edit Reading" class="rvoSecondary8" asp-page-handler="Edit" asp-route-date="@Model.StartDate.AddDays(i).ToString("dd-MM-yyy")" asp-route-systemName="@Model.SystemName" asp-route-companyID="@Model.CompanyID">@Model.StartDate.AddDays(i++).ToString("dd-MM-yyy")</a></td>
                                <td style="font-size:small;">@item.somevalue</td>
                                <td>other @item.values</td>
                            </tr>
                        }
                    }

    Tuesday, July 24, 2018 10:40 AM
  • Hi Stefan,

    Your questions have prompted me to think again about this issue and I have come up with a solution of sorts. Since I go to the db to show the readings on the web page I do have the exact date and time the reading was taken so I will use that datetime object in US format in the <a link on the page and just show the date to the user in GB format - dd-MM-yyyy instead of creating the date manually as I have been doing, which means that when I then go to retrieve the ID of the reading I can use the date in US format together with the time. Your help has been invaluable thanks very much. Since it does not seem possible to adjust the Linq query perhaps that is the best solution.

    Tuesday, July 24, 2018 11:05 AM
  • Ohh that doesn't work because I only have the actual reading to enter into the table but it is the plantReading ReadingTimeStamp I need to get the index :-(
    Tuesday, July 24, 2018 11:20 AM
  • Stefan, Just wanted to say I solved the problem by doing things a different way and not trying to use a date. Very many thanks for your help.
    • Marked as answer by Kiboko2 Tuesday, July 24, 2018 12:04 PM
    Tuesday, July 24, 2018 12:04 PM