1

I have a 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?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
tamil selvi
  • 69
  • 3
  • 2
    Can you please tell me what did you googled : "codes which i googled and tried also doesnt work" because there are a lot of solutions provided online and even here: https://stackoverflow.com/questions/8004645/how-to-get-the-week-day-name-from-a-date – VBoka Apr 07 '22 at 08:16
  • 1
    "Doesn't work" is the single most annoying phrase on SO. Please, show the error messages, show Exactly what didn't work, show what you got vs what you expected. Don't make us guess. – MatBailie Apr 07 '22 at 09:20

2 Answers2

1

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

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
-1

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];
be_real
  • 180
  • 1
  • 1
  • 12
  • 1
    The question is tagged Oracle and this is not valid Oracle syntax. It looks to be SQL Server code. – MT0 Apr 07 '22 at 10:18