Home

How to format dates in queries?

Examples:

myTable.Select( x=> x.myDate.ToString("yyyy-MM-dd") ).Dump() ;

or

myTable.Where( x=> x.myDate.ToString("yyyy-MM-dd") == "2020-01-01" ).Dump() ;

Comments

  • What are you asking?

    If you want to make yyyy-MM-dd the default, you can do so in Edit | Preferences > Results.

  • Hi friend,

    That was just an example. In fact, there will be several formats throughout the code.

    yyyy-MM-dd
    yyyyMMdd
    yyyyMM

    Is it possible to do inside the code?

  • edited July 23

    You should treat dates as date in sql, not string. Format the date outside sql and parse the date before using in query:

    //ExecuteCommand("CREATE TABLE myTable ( myDate DATETIME NOT NULL )");
    //ExecuteCommand("INSERT myTable VALUES ( '2020-01-01' ), ( '2020-01-03' ), ( '2020-03-02' )");
    
    myTable
      .Select(x => new { x.myDate }) // Select only the column(s) you'll need
      .AsEnumerable() // Convert IQueryable to IEnumerable (everything from here is performed / evaluated client side)
      .Select(x => x.myDate.ToString("dd-MM-yyyy")) // Format date
      .Dump();
    
    
    myTable
      .Where(x => x.myDate == DateTime.ParseExact("2020-01-03", "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture))
      .Dump();
    
Sign In or Register to comment.