C# How to convert the date that have been retrieve from DB and Where should I write?-CodePudding

In this task, I am trying to write a method that filters according to the date entered by the user. But I couldn't figure out where and how to write the format changing code.

I have a DateTime object in database like this :2022-08-09 11:42:26.3600000(y/m/d)

When the user enters the date in the following format : 09/08/2022, I am trying to show the data in the database in the following format : 09/08/2022 11:42 to the user. And when the user enters as d/m/y, the datas that matches this date will be returned as a list.

If it matches more than one data, it will return as a list.

this is my controller method:

[HttpGet("date/{date}")]
[Produces("application/json")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
public async Task<ActionResult<Result>> GetPartySearchHistoriesByDate([BindRequired] DateTime date)
{
  try
  {
    var items = await _mediator.Send(new GetPartySearchHistoriesByDateQuery() {Date = date});

    return Result.Ok(new
    {
      MatchCount = items.Count,
      Matches = items
    });
  }
  catch (Exception ex)
  {
            throw new SanctionException("An error occurred while fetching record.", ex);
  }
}

This is Handler :

public async Task<IList<PartySearchHistoryVM>> Handle(GetPartySearchHistoriesByDateQuery request, CancellationToken cancellationToken)
{
    var dates = await _partySearchHistoryRepository.GetListByDateAsync(request.Date);
    return dates.Adapt<IList<PartySearchHistoryVM>>();
}

this is dapper method:

public async Task<List<PartySearchHistoryDto>> GetListByDateAsync(DateTime dateTime)
{
    IUnitOfWork unitOfWork = await _dbConnection.GetUnitOfWorkAsync();
    unitOfWork.Begin();
    try
    {
        var partySearchHistories = await unitOfWork
            .Connection
            .QueryAsync<PartySearchHistoryDto>(
            @$"SELECT *
          FROM [dbo].[PartySearchHistories] WITH (NOLOCK)
          WHERE CreatedAt = @dateTime
            AND IsDeleted = 0",
            new { dateTime },
            transaction: unitOfWork.Transaction);

        return partySearchHistories.ToList();
    }
    catch (Exception)
    {
        return new List<PartySearchHistoryDto>();
    }
}

this code only works if you send exactly the format in the database as a parameter.

CodePudding user response：

As you are using a direct SQL query, try to do the conversion directly in it, this way:

"SELECT *
      FROM [dbo].[PartySearchHistories] WITH (NOLOCK)
      WHERE CONVERT(VARCHAR, CreatedAt, 103) = @dateTime
        AND IsDeleted = 0"