-1

I have 2 columns, date and price. The input will be 2 different dates and I want to get date and price between those (only workdays, no need to consider weekend days). How can I make this query in SQL? I really have no clue, thanks.

shankshera
  • 947
  • 3
  • 20
  • 45
  • You can use the `WEEKDAY()` function to get the day of week, and filter out weekends with that. – Barmar Oct 05 '13 at 12:43
  • Read this post: http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates – AgRizzo Oct 05 '13 at 12:43

3 Answers3

5
SELECT date, price
FROM table
WHERE date BETWEEN @start AND @end
AND WEEKDAY(date) < 5
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • This is cool seems to be working!Thanks!But one question is this WEEKDAY() works with daylight saving time also? – Ingus May 03 '17 at 06:16
  • @IngusGraholskis I think most places change to/from DST at 2am, so the day of the week doesn't change as a result. – Barmar May 03 '17 at 15:13
0

where date_field > @start_date and date_field < @end_date

You could also use the "between" keyword, but I always forget if that's inclusive or not.

As far as weekday goes, be very careful about using weekday(date_field) (as Barmar) suggested in the where clause as it could trigger that function being run on every row in the table which would negate any indexes you have. It's possible to use it and it be okay if the filter above is pretty stringent I think - you'll have to play with it and look at the explain statements.

Jody
  • 8,021
  • 4
  • 26
  • 29
0

There's a lot more to it than meets the eye. Really you need a calendar table per year that shows the workdays and excludes the weekends and public holidays ... which of course is region-dependent ...

user207421
  • 305,947
  • 44
  • 307
  • 483