Date formatting

[gunnermac]

Hi All,

I am fairly new to SQL and I have been looking around for a few days to find out the way to format a date in Oracle.

I don't want the time stamp to display and have tried using the:

to_char(fieldname,'DD-MON-YYYY')

command, but then the sort on this field is alphabetical.

Could someone please help a budding novice please.

 

[karluk]

If you don't specify a format, Oracle will display the date in whatever the default format is for your system. You can determine the default by noting the output of the query

SQL> select sysdate from dual;

SYSDATE
---------
21-NOV-01

If this format is acceptable, you can sort without doing any formatting at all. For example

select fieldname from your_table
order by fieldname;

Note that the sort will be in date order, not an alphabetical sort by the way the date is displayed.

 

[carp]

On the other hand, suppose you wanted to change the format but still sort on the date rather than the character string:

SELECT to_char(date_field,'Month,YYYY')
FROM my_table
ORDER BY date_field;

will display your data as character strings, but still sort on the underlying date/time.

 

[gunnermac]

Thanks for your replies.

I have found the problem. I started by trying the TRUNC command using MSQuery. It seems there is a limitation to the TRUNC command and it still displays the time stamp. I have tried it in PL/SQL and it worked fine.

Looks like Microsoft have strayed from the standards again!

 

[Guest_imported]

If you wrap the order field with the same formatting, you will get what you are looking for (see below);

SELECT to_char(date_field,'Month,YYYY')
FROM my_table
ORDER to_char(date_field,'Month,YYYY');

 

[Guest_imported]

Sorry I meant;

SELECT to_char(date_field,'Month,YYYY')
FROM my_table
ORDER BY to_char(date_field,'Month,YYYY');