Hi

Could someone help me with some syntax?

I know that

SELECT To_char (sysdate, 'ddth Month YYYY')

will give me the format I want if it is today's date that I need.

But suppose it is another date that I know - say 3rd February 2006?

I can't get the syntax right.

thanks

Liz

To convert a character representation to a date you use the to_date
function:

to_date('03-Feb-2006','DD-Mon-YYYY')

If you have a character representation of a date and you want to
reformat it but keep it in character format you can either use the
provide character manipulation functions like instr, substr, lenght,
replace, and transform or you can convert the data to a date with
to_date and enclose the to_date in a to_char to convert the data back
to character representation.

Normally you to_date the data into the database one time and use
to_char to display the data as desired.

All functions mentioned in my reply are described in the SQL manual
chapter on single row functions.

HTH -- Mark D Powell --

"Liz J" <thejordanschangethistoanatsignclara.net> wrote


How about:

To_char ('03-Feb-2006', 'ddth Month YYYY')

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Randy Harris wrote:

literal string '03-Feb-2006' into a valid date, and assumes that the
default NLS_DATE_FORMAT string is set to 'dd-mon-yyyy'.

A safer method would be
to_char(to_date('03-Feb-2006', 'dd-mon-yyyy'), 'ddth Month YYYY')

:
: "Martin Doherty" <martin.doherty (AT) undisclosed (DOT) com> wrote

: Randy Harris wrote:
:
: : "Liz J" <thejordanschangethistoanatsignclara.net> wrote in message
: : news:1140653404.29042.0 (AT) damia (DOT) uk.clara.net...
: :
: : How about:
: :
: : To_char ('03-Feb-2006', 'ddth Month YYYY')
: :
:
: That might work, but it relies on an implicit type conversion of the
literal string '03-Feb-2006' into a valid date, and assumes
: that the default NLS_DATE_FORMAT string is set to 'dd-mon-yyyy'.
:
: A safer method would be
: to_char(to_date('03-Feb-2006', 'dd-mon-yyyy'), 'ddth Month YYYY')
:

better yet:

to_char(date '2006-02-03', 'ddth Month YYYY')

++ mcs

"Many thanks everyone

Liz

"Liz J" <thejordanschangethistoanatsignclara.net> wrote

Liz, the solutions suggested by Mark and Martin are much better than the one
I offered, since they don't depend on implicit type coercion.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.