to_char syntax

Liz J · *nix forums beginner Joined: 01 Aug 2005 Posts: 4

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

Mark D Powell · *nix forums Guru Joined: 23 Apr 2005 Posts: 701

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 --

Randy Harris · *nix forums Guru Wannabe Joined: 23 Apr 2005 Posts: 202

"Liz J" <thejordanschangethistoanatsignclara.net> wrote in message
news:1140653404.29042.0@damia.uk.clara.net...

Martin Doherty · *nix forums beginner Joined: 10 Jan 2006 Posts: 23

Randy Harris wrote:

Mark C. Stock · *nix forums Guru Joined: 05 May 2005 Posts: 730

:
: "Martin Doherty" <martin.doherty@undisclosed.com> wrote in message
news:7YkLf.30$AP2.42@news.oracle.com...
: Randy Harris wrote:
:
: : "Liz J" <thejordanschangethistoanatsignclara.net> wrote in message
: : news:1140653404.29042.0@damia.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

Liz J · *nix forums beginner Joined: 01 Aug 2005 Posts: 4

"Many thanks everyone

Liz

Randy Harris · *nix forums Guru Wannabe Joined: 23 Apr 2005 Posts: 202

"Liz J" <thejordanschangethistoanatsignclara.net> wrote in message
news:1140738562.18261.0@damia.uk.clara.net...

Google