How to format dates in queries?

manajubi

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() ;

JoeAlbahari

What are you asking?

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

manajubi

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?

nescafe

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();

manajubi

Very good. Thank you.