Hi,

Is it possible to use TRUNC function to get the weekday (e.g.
Monday, Tuesday ...)? I know I can use TO_CHAR to get it:

select TO_CHAR(trunc(sysdate), 'DAY') weekday, TO_CHAR(sysdate, 'D')
day_of_week from dual;

Thanks in advance.

Nick Li

On Apr 21, 5:26*pm, nickli2... (AT) gmail (DOT) com wrote:

I would say the proper way to ge the day of the week from a date data
type is to use to_char otherwise the default value behavior for the
componenets might be an issue since the result of a trunc is still a
date data type.

1* select to_char(trunc(sysdate,'YYYY'),'YYYYMMDD HH24:MI:SS') from
dual
UT1 > /

TO_CHAR(TRUNC(SYS
-----------------
20090101 00:00:00

UT1 > c /YYYY/MM/
1* select to_char(trunc(sysdate,'MM'),'YYYYMMDD HH24:MI:SS') from
dual
UT1 > /

TO_CHAR(TRUNC(SYS
-----------------
20090401 00:00:00

UT1 > c /MM/DD/
1* select to_char(trunc(sysdate,'DD'),'YYYYMMDD HH24:MI:SS') from
dual
UT1 > /

TO_CHAR(TRUNC(SYS
-----------------
20090422 00:00:00

UT1 > l
1 declare
2 v_day varchar2(12);
3 begin
4 select trunc(sysdate,'DAY') into v_day from dual;
5 dbms_output.put_line(v_day);
6* end;
UT1 > /
19-APR-09 <= notice date change from today the 22nd to first
day of this week

PL/SQL procedure successfully completed.

UT1 > select to_char(trunc(sysdate),'DAY') from dual; -- no trunc
necessary, just use to_char

TO_CHAR(T
---------
WEDNESDAY


I suggest using to_char to get and work with components of a date data
type.

HTH -- Mark D Powell --

nickli2000 (AT) gmail (DOT) com wrote:
: Hi,

: Is it possible to use TRUNC function to get the weekday (e.g.
: Monday, Tuesday ...)? I know I can use TO_CHAR to get it:

Perhaps a misunderstanding.

TRUNC(date) returns a new date that is smaller than or equal to the
original date.

An Oracle DATE always includes a time portion, so trunc can never return a
"day", the closest it ever comes is to returning a date where the time
portion corresponds to the start of a day.

Some trunc(date) options involve day periods, such as the default trunc
option which truncs the time portion to be the time corresponding to the
start of the day (i.e. it sets the hours, minutes, and seconds to 0), and
one or more trunc options returns the date that is the start of the day
that is the start of the week containing the original date.

If you mean to get the "name" of a day then you need to convert the date
to a string. In code you should use always TO_CHAR, though the default
date to string conversions are convenient in interactive queries.

TO_CHAR has various output formats for dates, google: [oracle date format
models] for more details. For example to_char(sysdate,'DY') returns the
abreviation of the name of today.

NEXT_DAY() returns dates corresponding to particular days of the week that
come after a specified date.

$0.10