CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Ridiculon
Ridiculon

Reputation: 525

How do you take a date in one format as input and output a date in a different format in Oracle SQL?

I need to take in a date as a parameter in a particular format ('YYYY-MM-DD') and output the same date in another format ('Day Month DD, YYYY').

So far I have been trying to extract each element from the input so I can stitch it together again in the right format, an issue I'm running into though is that the Day extraction portion of this is basically just taking the day of the month number I feed it and outputting a mod 7 day lookup, which is useless.

SELECT
    TO_CHAR( TO_DATE( EXTRACT( DAY FROM (TO_DATE( :input_date, 'YYYY-MM-DD'))), 'DD'), 'Day') as "day_word",
    TO_CHAR( TO_DATE( EXTRACT( MONTH FROM (TO_DATE(:input_date, 'YYYY-MM-DD'))), 'MM'), 'Month') as "month"
FROM
    dual

I'd really love it if I could avoid this sort of manual string manipulation in SQL (cause it sucks). Please tell me there's a better method!

Upvotes: 0

Views: 117

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Why not just use to_char()?

select to_char(to_date(:work_date, 'YYYY-MM-DD'), 'DAY Month DD, YYYY')

Upvotes: 1

Related Questions