| to_char(sysdate, 'day') issue [message #119746] |
Sat, 14 May 2005 11:51  |
danny_t
Messages: 52
Registered: March 2005
|
Member |
|
|
Can someone please explain the following:
SQL> SELECT to_char(sysdate, 'day') FROM dual;
TO_CHAR(S
---------
saturday
SQL> SELECT * FROM tbl_calendar_config WHERE day_of_week = 'saturday';
DAY_OF_WE TRAILER_D START_TIM END_TIME
--------- --------- --------- ---------
saturday 13:00 09:00 18:00
SQL> SELECT * FROM tbl_calendar_config WHERE day_of_week = to_char(sysdate, 'day');
no rows selected
In my calendar table i have 7 records the first field showing values of 'monday' to 'sunday'. I wish to send in a date and return the row relating to the day of that date.
I.e. in the example above the date sent in is sysdate which is 'saturday' why does 'saturday's row not get returned?
Many Thanks
[Updated on: Sat, 14 May 2005 11:52] Report message to a moderator |
|
|
|
| Re: to_char(sysdate, 'day') issue [message #119748 is a reply to message #119746] |
Sat, 14 May 2005 13:42  |
 |
Barbara Boehmer
Messages: 9091
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The value of to_char(sysdate,'day') is actual 'saturday ', with a space after saturday. Oracle pads the value to the length of the longest day, which is wednesday, which is nine characters. So, when comparing values, you need to either rpad the value in your table to 9 characters or rtrim the value returned by to_char(sysdate,'day') or use the fm toggle to eliminate the extra space. Please see the demonstration below.
scott@ORA92> SELECT TO_CHAR (SYSDATE, 'day'),
2 LENGTH (TO_CHAR (SYSDATE, 'day'))
3 FROM dual
4 /
TO_CHAR(S LENGTH(TO_CHAR(SYSDATE,'DAY'))
--------- ------------------------------
saturday 9
scott@ORA92> CREATE TABLE tbl_calendar_config
2 (day_of_week VARCHAR2(10))
3 /
Table created.
scott@ORA92> INSERT INTO tbl_calendar_config VALUES ('saturday')
2 /
1 row created.
scott@ORA92> SELECT *
2 FROM tbl_calendar_config
3 WHERE day_of_week = 'saturday'
4 /
DAY_OF_WEE
----------
saturday
scott@ORA92> SELECT *
2 FROM tbl_calendar_config
3 WHERE day_of_week = to_char(sysdate, 'day')
4 /
no rows selected
scott@ORA92> SELECT *
2 FROM tbl_calendar_config
3 WHERE RPAD (day_of_week, 9) = to_char(sysdate, 'day')
4 /
DAY_OF_WEE
----------
saturday
scott@ORA92> SELECT *
2 FROM tbl_calendar_config
3 WHERE day_of_week = RTRIM (to_char(sysdate, 'day'))
4 /
DAY_OF_WEE
----------
saturday
scott@ORA92> SELECT *
2 FROM tbl_calendar_config
3 WHERE day_of_week = to_char(sysdate, 'fmday')
4 /
DAY_OF_WEE
----------
saturday
scott@ORA92>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
