'Typeorm. use only the date part while querying via date and exclude the timestamp part

I have a use case where I have to retrieve a users record from the database via his date of birth. The thing is that we have stored the date of birth of the user as datetime object which makes it very difficult to retrieve the users data, as we can not provide the exact date with timestamp.

I tried looking for a functions support in typeorm using which I can just compare the provided date with only the date part of the birth date in database but did not find any reference anywhere.

Solution 1:^[1]

Found the solution, We can actually use the LIKE operator, the only prerequisite is that we would have to convert the date to string,

something like

date_of_birth::text LIKE '2011-01-%'

Solution 2:^[2]

Don't convert your column to a string. Here's how to do it with a date/datetime/timestamp.

import { startOfDay, endOfDay } from 'date-fns';
import { Between, Equal } from "typeorm";
//...
let findArgs = { 
  where:{
    date: Between(startOfDay(webInputArgs.date).toISOString(), endOfDay(webInputArgs.date).toISOString()), 
    userId: Equal(ctx.req.session.userId)
  }
};
return entity.find(findArgs) as any;

Solution 3:^[3]

You can use the query builder and the postgresql DATE_TRUNC() function:

const usersBornToday = await getManager()
    .createQueryBuilder(User, "user")
    .where(`DATE_TRUNC('day', "birthdatetime") = :date`, {date: '2021-03-27'})
    .getMany()

Solution 4:^[4]

If the DB type is MySQL, the CONVERT function will achieve the same approach used by @Vijender, which is to convert timestamp to text as below:

let date = '2022-01-25'; //yyyy-mm-dd, for Regex validation - /\d{4}\-\d{2}\-\d{2}/
date = `${date}%`;
queryResult.where('CONVERT(date_of_birth, char) LIKE :date', { date });

Solution 5:^[5]

Todo:only show today stock list
      const start = moment().startOf('day').toDate();
      const end = moment().endOf('day').toDate();
     
      const query=getManger()
        .createQueryBuilder(stocks, 'stocks')
        .andWhere('stocks.createdAt BETWEEN :start AND :end', { start, end })

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Vijender Kumar
Solution 2
Solution 3	sarfata
Solution 4	SNOSeeds
Solution 5	Hussain

'Typeorm. use only the date part while querying via date and exclude the timestamp part

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]