CODEWITHSUNDEEP
sqloracle-database
MoshMosh
MoshMosh

Reputation: 71

how to extract day in char in oracle

i would like to extract the day in char in the following code:

SELECT DN_DETAILS.DN,DN_WORKING_HOURS.DAY,
       DN_WORKING_HOURS.BEGIN_HOUR,
       DN_WORKING_HOURS.END_HOUR 
FROM DN_DETAILS 
  JOIN DN_WORKING_HOURS ON DN_DETAILS.DN=DN_WORKING_HOURS.DN
WHERE DN_WORKING_HOURS.DAY = (SELECT TO_CHAR(TO_DATE('03/10/2016','DD/MM/YYYY'), 'Day') FROM DUAL);

the col DN_WORKING_HOURS.DAY has days in the form 'Sunday','Monday' etc...

whats wrong with this:

(SELECT TO_CHAR(TO_DATE('03/10/2016','DD/MM/YYYY'), 'Day') FROM DUAL)

don't know what i'm doing wrong

Upvotes: 0

Views: 500

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

By default the Day format element is padded to the length of the longest day name in your date language; which in English is Wednesday at 9 characters. You can see the padding with:

select '<'|| to_char(sysdate + level, 'Day') ||'>' from dual connect by level <= 7;

'<'||TO_CHAR(SYSDATE+LEVEL,'DAY')||'>'
--------------------------------------
<Thursday >
<Friday   >
<Saturday >
<Sunday   >
<Monday   >
<Tuesday  >
<Wednesday>

You can add the FM format model modifier to suppress that padding:

select '<'|| to_char(sysdate + level, 'FMDay') ||'>' from dual connect by level <= 7;

'<'||TO_CHAR(SYSDATE+LEVEL,'FMDAY')||'
--------------------------------------
<Thursday>
<Friday>
<Saturday>
<Sunday>
<Monday>
<Tuesday>
<Wednesday>

So, assuming your table's Day column is varchar2 and not padded too, your subquery needs to be:

(SELECT TO_CHAR(TO_DATE('03/10/2016','DD/MM/YYYY'), 'FMDay') FROM DUAL)

... or as @a_horse_with_no_name mentioned do the same thing without a subquery:

...
WHERE DN_WORKING_HOURS.DAY = TO_CHAR(TO_DATE('03/10/2016','DD/MM/YYYY'), 'FMDay');

Bear in mind that day names are NLS-dependent so if this might ever be run in a non-English environment you could make it safer with:

...
WHERE DN_WORKING_HOURS.DAY = TO_CHAR(TO_DATE('03/10/2016','DD/MM/YYYY'), 'FMDay',
  'NLS_DATE_LANGUAGE=ENGLISH');

Upvotes: 3

Related Questions