Using day of the week in SQL query

Queries and Resources
1

I am trying to pull shipping records that have a certain ship date. Right now, the company does not ship anything on Fridays, Saturdays or Sundays. So I am trying to use a query that will skip over those days. Here is what I was trying to use:

SELECT *
FROM shipping
WHERE ship_date = {{ moment(new Date()).add(2, 'days').format("ddd") = 'FRI' ? moment(new Date()).add(6, 'days') : moment(new Date()).add(2, 'days') }}
GROUP BY id, dealer
ORDER BY ship_date

I am getting the error message "ReferenceError: cannot assign to function call". Dates are something that I always struggle with so if anyone could assist me, I would appreciate it.

2 Likes
3

Hi @tomm,

If you just want to select everything where the ship_date ISN'T Fri/Sat/Sun, you can do something like this, assuming you're using postgres.

SELECT *
FROM shipping
WHERE EXTRACT(DOW FROM ship_date) NOT IN (5, 6, 0)
GROUP BY id, dealer
ORDER BY ship_date

That will get the day of the week as a numeric value of 0-6 (0 being Sunday) and then exclude Fri/Sat/Sunday in your query.

1 Like
4

This is a great suggestion from MikeCB!

One gotcha that has gotten me on using this method is accidentally having my date field stored as text instead of date in my table which led to this function failing. If you got that set as it should be then it should be all good!