SQL date functions

Question

create table sales
(
     sdate DATE,
     samount varchar2(10)
);

insert into sales values('1-FEB-2016',25000);
insert into sales values('2-FEB-2016',45000);
insert into sales values('3-FEB-2016',25000);
insert into sales values('4-FEB-2016',29000);
insert into sales values('5-FEB-2016',75000);
insert into sales values('6-FEB-2016',90000); 
insert into sales values('7-FEB-2016',38000); 

select 
    to_char(sdate, 'day') 
from sales;

select *
from sales 
where to_char(sdate, 'day') = 'monday';

Anything wrong with the last line?

The output shown is no data found instead of 1-FEB-2016 25000

Please tag your question with the database you are using. – Gordon Linoff Apr 02 '16 at 13:30 — Gordon Linoff, Apr 02 '16 at 13:30

Gordon Linoff · Answer 1 · 2016-04-02T13:59:45.017

1

I believe the reason is that when using the 'day' format option, Oracle puts spaces to pad the length out to 'wednesday' (the longest date name). You can check this by seeing if this works:

where to_char(sdate, 'day') = 'wednesday'

I use the abbreviated forms, anyway, because it is easier to type:

where to_char(sdate, 'dy') = 'mon';

This behavior is even explained in the documentation:

The character elements MONTH, MON, DAY, and DY are padded with trailing blanks to the width of the longest full month name, the longest abbreviated month name, the longest full date name, or the longest abbreviated day name, respectively, among valid names determined by the values of NLS_DATE_LANGUAGE and NLS_CALENDAR parameters.

edited Apr 02 '16 at 13:59

answered Apr 02 '16 at 13:33

Gordon Linoff

1,242,037
58
646
786

where to_char(sdate,'dy') = 'mon' is working well for all days but where to_char(sdate,'day') = 'monday' is not working – zaolee_dragon Apr 02 '16 at 13:45
@aneela . . . I don't understand your comment. You specify that in the question and I think my answer explains what is happening. – Gordon Linoff Apr 02 '16 at 14:00

score 1 · Answer 2 · answered Apr 02 '16 at 13:41

1

You should use UPPER(to_char(sdate,'fmday'))= 'MONDAY'. Generally there is a space padding for which its showing NO_DATA found error.

answered Apr 02 '16 at 13:41

Avrajit Roy

3,233
1
13
25

score 0 · Answer 3 · answered Apr 02 '16 at 13:31

0

You should put qoutes around your second value parameters because they are of varchar type or change the type to integer

answered Apr 02 '16 at 13:31

Marko vlaić

316
5
15

sql_dummy · Answer 4 · 2016-04-03T11:47:17.823

0

   select * from sales where to_char(sdate, 'fmday') = 'monday';

Oracle database is designed in such a way that all spellings of day require same amount of space. So since the length wednesday is 9 which is the max length among all days, other days are padded with a blank () if their length is less than 9. So to_char(sdate, 'fmday') does not return monday (6 letters) but instead it is returns monday' ' (9 letters i.e 6 letters plus 3 additional spaces). To remove those additional blanks you need to use fm (the date type format model used to remove additional blanks in date)

edited Apr 03 '16 at 11:47

answered Apr 03 '16 at 10:18

sql_dummy

715
8
23

Please explain how your answer solves the problem, it will help everyone understand your solution with more clarity and for future reference. – Aziz Apr 03 '16 at 11:35

score -2 · Answer 5 · answered Apr 02 '16 at 14:04

-2

Gap between "*" and "from"...

Thanks.

answered Apr 02 '16 at 14:04

Rajesh Ranjan

537
2
12

Would generate an error if that was the cause, not an empty result set. – Clockwork-Muse Apr 02 '16 at 14:07

SQL date functions

5 Answers5