extracting day from timestamp date column in oracle-sql

user_14734870 0
Viewed 91

I have a table sample below

ENABLED    FILE_NAME            SCHEDULED_DATE
1          TEST_20201101.CSV    01-NOV-20 11.00.00 PM
1          TEST_20201102.CSV    02-NOV-20 11.00.00 PM
1          TEST_20201103.CSV    03-NOV-20 11.00.00 PM
1          TEST_20201104.CSV    04-NOV-20 11.00.00 PM
1          TEST_20201105.CSV    05-NOV-20 11.00.00 PM
1          TEST_20201106.CSV    06-NOV-20 11.00.00 PM
..
..
..data till Nov30.

I want to update enabled column to 0 for all days where it is Saturday/Sunday. Tried using extract() but it returns day number for a date eg: extract on 2020-Nov-27 will return 27

sql-update oracle sql
2 Answers

You can use to_char():

update t
    set enabled = 1
    where to_char(scheduled_date, 'Dy') in ('Sun', 'Sat');
user_11440350

The below query can help you to get the particular day from a time

 select to_char(SHCEDULED_DATE,'Day') from date_stack

Below is to update

update date_stack set enabled='0' where SHCEDULED_DATE in (select SHCEDULED_DATE from date_stack where trim(to_char(SHCEDULED_DATE,'Day'))='Saturday' or trim(to_char(SHCEDULED_DATE,'Day'))='Sunday' )

enter image description here

user_29862790
Related