Oracle Date Formatting – Best Practices and Examples

datedate formatoracle

this query:

select to_char(sysdate, 'Ddspth MONTH, Yyyysp') from dual

will return:

Eighteenth MARCH , Two Thousand Fourteen

i am wondering if there are any additional formatting keywords that will allow including a list of characters like "of" , "the" … inorder to have an output like:

"Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen

or is that requires splitting the date conversion and using a concat function? thanks

for example :

select 'Today is the ' || to_char(sysdate, 'Ddspth') || ' of' || to_char(sysdate,' MONTH, Yyyysp') from dual

Best Answer

figured this out, here is a solution in case someone needs to do something similar...

using double quotes "" around characters will display them as they are...

select to_char(sysdate,'"Today is the" Ddspth "of" fmMONTH,Yyyysp') as Today from dual

will display:

"Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen

Related Solutions

How to Change Default Date Format in Oracle

You can try the ALTER SESSION

ALTER SESSION SET NLS_DATE_FORMAT = '[date_format]';

date_formats

MM  Numeric month (e.g., 07)
MON     Abbreviated month name (e.g., JUL)
MONTH   Full month name (e.g., JULY)
DD  Day of month (e.g., 24)
DY  Abbreviated name of day (e.g., FRI)
YYYY    4-digit year (e.g., 1998)
YY  Last 2 digits of the year (e.g., 98)
RR  Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM)  Meridian indicator
HH  Hour of day (1-12)
HH24    Hour of day (0-23)
MI  Minute (0-59)
SS  Second (0-59)

TO_CHAR(datum_column, date_format) is the best method to avoid headpains check Justin Cave's answer

Mysql – SQL CHANGE DATE FORMAT IN BULK

Plan A: Clean up the dates as you load the data. How are you loading it? INSERT? LOAD DATA INFILE? It is possible to do the STR_TO_DATE() in either case.

Plan B: (This requires "Plan A" for future inserts.) Devise a script, possibly mostly inside a Stored Routine that would do the following for any given column known to be a VARCHAR that should be a DATE. (Do something similar for a DATETIME.) Here is a sketch of what is needed for one $column in one $table:

ALTER TABLE $table ADD COLUMN tmp DATE NOT NULL;
UPDATE $table SET tmp = STR_TO_DATE($column, '%d/%m/%Y');
ALTER TABLE $table DROP COLUMN $column, RENAME COLUMN tmp TO $column;

Best Answer

Related Solutions

How to Change Default Date Format in Oracle

Mysql – SQL CHANGE DATE FORMAT IN BULK

Related Question