How to format dates before save to database

Hello friends
Can i format the created_at (timestamps) field of my migration?
I would like to save this format like “2020-07-11”. That would facilitate my queries when search for date, because html date input returns the same format.

image

http://127.0.0.1:3333/searchbydate?date=2020-07-11

If you want to easily search for “2020-07-07”, I will advise that you cast the months and days less than 10 to Number type instead of changing the way the date is saved in the database.

That can easily be achieved with:


const searchDate = params.date // "2020-07-07"
const { year, month, date } = searchDate.split("-") 

const services = await Service.query()
  .select("services.id", "services.service_date")
  .whereRaw(`services.service_date = '${year}-${month * 1}-${day * 1}'`) // Searches for 2020-7-7 which is compatible with the database
  .fetch();

1 Like

hi ndianabasi. Thanks for the help. But no success yet.

Thats my code:

async searchpordata({request, response, session, view}) {

let query2 = request.input('data')

const { year, month, date } = query2.split("-") 

const notas = Nota.query()
  .select("notas.created_at")
  .whereRaw(`notas.created_at = '${year}-${month * 1}-${day * 1}'`) // Searches for 2020-7-11 which is compatible with the database
  .fetch();

My view

@layout('layout.app')

@section('content')
   
<!--BUSCAR-->

<form action="/buscarpordata" method="GET">
    <h1 class="title is-4 column is-4 is-offset-4">Buscar Nota por Data</h1>
  <div class="field is-grouped column is-4 is-offset-4">
    
<input type="date" name="data" required>
  
    <br><div  style="margin-bottom: 20px;">
    <p class="control">
        <input type="submit" class="button is-primary is-medium is-6" value="Buscar">
    </p>
    </div>
 
  </div>
  </form>

@endsection

Apologies. Please add await.

const notas = await Nota.query()
  .select("notas.created_at")
  .whereRaw(`notas.created_at = '${year}-${month * 1}-${day * 1}'`) // Searches for 2020-7-7 which is compatible with the database
  .fetch();
1 Like

thanks. But using your query, where i put my “query2” to compare to database created_at field? “query2” variable has the value of my chosen date :slight_smile:

What do you get when you log query2 and notas?

Try to serialise notas to JSON and log:

//Instead of:
const notas = await ...

//Do:
let notas = await ....
notas = notas.toJSON()

//Then log notas
console.log(notas)

I think with more debugging you should be able to solve the problem.

2 Likes