DT
Drizzle Team•13mo ago
JavaDad

How to select part of a string in Drizzle?

I have dates in ISO format (Stored as Strings) in my SQLite database. I need to be able to find a match only querying the year-month-day. Attached are two different ways I have tried this and neither seem to work. 
const timers = await db.select().from(timerSchema).where(sql`to_tsvector ('simple', '${timerSchema date}') @@ to_tsquery('simple', '${queryDayString}'))`;
const timers = await db.select().from(timerSchema).where(sql`to_tsvector ('simple', '${timerSchema date}') @@ to_tsquery('simple', '${queryDayString}'))`;
const timers = await db.select().from(timerSchema).where(sql`timer_date like '%${queryDayString}%'`);
const timers = await db.select().from(timerSchema).where(sql`timer_date like '%${queryDayString}%'`);
2 Replies
JavaDad
JavaDadOP•13mo ago
Update: Seems like this is the solution. 
const timers = await db
      .select()
      .from(timerSchema)
      .where(sql`timer_date like ${`%${queryDayString}%`}`);
const timers = await db
      .select()
      .from(timerSchema)
      .where(sql`timer_date like ${`%${queryDayString}%`}`);
Mykhailo
Mykhailo•13mo ago
Hello @JavaDad! I think you can use DATE_TRUNC function: 
 const date = '2023-12-22';
 const result = await db
   .select()
   .from(users)
   .where(sql`DATE_TRUNC('day', "myDate") = ${date}`);
 const date = '2023-12-22';
 const result = await db
   .select()
   .from(users)
   .where(sql`DATE_TRUNC('day', "myDate") = ${date}`);
oh, sorry, my bad, I thought about PostgreSQL and didn't notice that you need for SQLite 😅

Did you find this page helpful?