How to select only the days of the week?

Asked

Viewed 4,268 times

2

How do I take only weekdays, excluding weekends, using SQL?

I need to notify the customer about some debt, and if it is Friday I can’t charge it because it will start the weekend, he will have Saturday and Sunday to pay then he will only be notified on Monday if he has not made the payment.

SELECT P.dtvenc, 
FROM foo P
WHERE p.dtvenc = To_date(SYSDATE)-1
sql

  • Try to keep the code on the questions reduced to the minimum necessary for your understanding, okay? The original code was immense and most had nothing to do with the problem.

  • 3

    Actually, Wesley, this has to do with this topic from the help center: How to create a Minimum, Complete and Verifiable example, but the ideal would be for you to do this and not an editor.

  • OK, I’ll follow the advice!!! Thank you

1 answer

5


Depends on the DBMS you use.

If you are SQL Server, use the function DATEPART. I will leave it to you to read the documentation on the link. The form would be as follows:

WHERE DATEPART(p.dtvenc, 'weekday') BETWEEN 2 AND 6

Where 2 and Monday and 6 it’s Friday.

If it is Oracle, you refer to the form of the function to_char that receives a date.

WHERE to_char(p.dtvenc, 'D') in ('2', '3', '4', '5', '6')

Editing: The @Caffé called attention to the fact that the number corresponding to each day of the week in Oracle may vary. This depends on the language/culture set up. For more details (if you are curious or have problems with the numbers), I suggest searching for National Language Support (the link that Caffé commented).

If it is Mysql, Postgresql or other system... In this case you expect someone who knows them to answer here. Tag the bank you use in the question helps you get better answers.

  • Thank you Renam... I will test this procedure of Oracle, in case my Bank and Oracle same.

  • Renan, it didn’t work, it doesn’t bring me any results. I used WHERE to_char(p.dtvenc) in ('2', '3', '4', '5', '6')

  • 2

    To return the number corresponding to the day of the week, the second parameter in the to_char, thus: ... to_char(p.dtvenc, 'D') .... Remembering that the number corresponding to Monday may vary according to the settings NLS, and an extra parameter can be added to to_char or a session setting can be performed to ensure that the command works as expected regardless of the server settings. See: http://www.orafaq.com/wiki/NLS

  • 1

    Thanks, Caffé! I edited here :)

  • thank you very much Renan managed to solve!!

  • @Renan want to complete with Mysql? function WEEKDAY(date) https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_weekday

  • @Everson you can edit my question again to include Mysql, or you can add an answer and win my vote :)

Show 2 more comments

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.