How to find the day name?-CodePudding

I have a SQL table named nifty_50 with the below two columns.


    Date              Close
Apr 07, 2022        17,763.40
Apr 06, 2022        17,807.65
Apr 05, 2022        17,957.40
Apr 04, 2022        18,053.40
Apr 01, 2022        17,670.45
Mar 31, 2022        17,464.75

I am trying to add another column which says the day name of the Date column.

Expected Output:


    Date              Close        Day_name
Apr 07, 2022        17,763.40      Thursday
Apr 06, 2022        17,807.65      Wednesday
Apr 05, 2022        17,957.40      Tuesday
Apr 04, 2022        18,053.40      Monday
Apr 01, 2022        17,670.45      Friday
Mar 31, 2022        17,464.75      Thursday

i tried doing

select  date, close, datename(date ,getdate()) as day_name, from nifty_50;

The above code doesn't work and all the other codes which i googled and tried also don't work. I know this is a simple code. can someone please help me with this?

CodePudding user response：

Use the TO_CHAR function with the Day format model.

select  "DATE",
        close,
        TO_CHAR("DATE", 'fmDay') as day_name
from    nifty_50;

Note: fmDay will strip trailing spaces from the day names (otherwise the days will all be output as strings of length equal to the longest day name padded with trailing spaces).

Note 2: DATE is a reserved word in Oracle and cannot be used as an unquoted identifier. It is better practice to pick a different identifier instead of using reserved words.

Which, for the sample data:

CREATE TABLE nifty_50 ("DATE", Close) AS
SELECT DATE '2022-04-07', 17763.40 FROM DUAL UNION ALL
SELECT DATE '2022-04-06', 17807.65 FROM DUAL UNION ALL
SELECT DATE '2022-04-05', 17957.40 FROM DUAL UNION ALL
SELECT DATE '2022-04-04', 18053.40 FROM DUAL UNION ALL
SELECT DATE '2022-04-01', 17670.45 FROM DUAL UNION ALL
SELECT DATE '2022-03-31', 17464.75 FROM DUAL;

Outputs:

DATE CLOSE DAY_NAME

2022-04-07 00:00:00 17763.4 Thursday

2022-04-06 00:00:00 17807.65 Wednesday

2022-04-05 00:00:00 17957.4 Tuesday

2022-04-04 00:00:00 18053.4 Monday

2022-04-01 00:00:00 17670.45 Friday

2022-03-31 00:00:00 17464.75 Thursday

DATE	CLOSE	DAY_NAME
2022-04-07 00:00:00	17763.4	Thursday
2022-04-06 00:00:00	17807.65	Wednesday
2022-04-05 00:00:00	17957.4	Tuesday
2022-04-04 00:00:00	18053.4	Monday
2022-04-01 00:00:00	17670.45	Friday
2022-03-31 00:00:00	17464.75	Thursday

db<>fiddle here

CodePudding user response：

You can try below to fetch Day name:

DECLARE @DateVal DATE = '2022-07-04';
SELECT @DateVal As [Date], 
    DATENAME(WEEKDAY, @DateVal) AS [Day Name],
    DATENAME(DW, @DateVal) AS [Day Name],
    DATENAME(W, @DateVal) AS [Day Name];