-2

I get input as 2011/11/13 00:00:00. So I made the query as:

select * from xxcust_pfoa434p_vw 
where week_ending_date = to_date(substr(:value,1,10),'YYYY/MM/DD')

The same statement gives proper result when queried against other tables. But throws error when I query this against the view xxcust_pfoa434p_vw

I have a view xxcust_pfoa434p_vw which has a column week_ending_date of date data type. The value in that column is like 3/2/2014,12/25/2011 i.e. MM/DD/YYYY

Even 

select * from xxcust_pfoa434p_vw where week_ending_date='3/2/2014'

also gives ORA-01843: not a valid month. What is the cause for this error.

Mike Deluca
  • 1,295
  • 2
  • 18
  • 41
Gayathri
  • 1
  • 3

3 Answers3

1

You say

"The same statement gives proper result when queried against other tables. But throws error when I query this against the view xxcust_pfoa434p_vw"

So clearly the problem is with the view. You also say

"[the view] has a column week_ending_date of date data type. The value in that column is like 3/2/2014,12/25/2011 i.e. MM/DD/YYYY "

Those values would only display like that if the default date mask for you system were MM/DD/YYYY. This is easy enough to check with the query

select * from V$NLS_PARAMETERS
where parameter = 'NLS_DATE_FORMAT';

Personally, my money is on that column not being a date column. ORA-01841 always indicates oracle attempting to cast a string into a date and finding a value which doesn't fit the explicit or default format mask. Plus the so-called date '3/2/2014' lacks leading zeroes and that's suspicious too.

I think whoever wrote that view decided to fix the format of week_ending_date and so deployed TO_CHAR to present a string not a date datatype. A DESC in SQL*Plus or looking at the view TEXT in ALL_VIEWS will reveal the answer.

APC
  • 144,005
  • 19
  • 170
  • 281
0

Even if you see formatted date in this format - it is only a visual representation, when oracle process your query it automatically convers string given by you into its own interal representation.

It is always better to use proper SQL one YYYY-MM-DD:

for 2nd march: select * from xxcust_pfoa434p_vw where week_ending_date = to_date('2014-03-02', 'YYYY-MM-DD')

for 3rd february: select * from xxcust_pfoa434p_vw where week_ending_date = to_date('2014-02-03', 'YYYY-MM-DD')

this conforms to SQL standard and do not produce confusion between DD/MM/YYYY and MM/DD/YYYY

Just quote from standard:

There is an ordering of the significance of <datetime field>s. This is, from most significant to least significant: YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

UPDATE: it is very good idea always use to_date function to specify exact format and avoid dependancy on any kind of localization settings

Iłya Bursov
  • 23,342
  • 4
  • 33
  • 57
  • I tried both and getting error"literal does not match format string" – Gayathri Feb 15 '14 at 04:20
  • @Gayathri looks like you have to use to_date, check this out: http://stackoverflow.com/questions/13518506/oracle-literal-does-not-match-format-string-error – Iłya Bursov Feb 15 '14 at 07:25
  • @Lashene I tried thie, but didn't work select * from xxcust_pfoa434p_vw where week_ending_date = to_date('2013/11/13','YYYY/MM/DD') – Gayathri Feb 15 '14 at 07:48
0
select * from xxcust_pfoa434p_vw 
where week_ending_date=to_date('03/02/2014','MM/DD/YYYY');
Sai
  • 659
  • 4
  • 12
  • 21
  • Sai, my query select * from xxcust_pfoa434p_vw where week_ending_date = to_date(substr(:value,1,10),'YYYY/MM/DD') works fine with other table but not with the view. I tried the query which you have given. Getting error "ORA-01841: (full) year must be between -4713 and +9999, and not be 0" – Gayathri Feb 15 '14 at 07:37
  • Gayathri, First provide date format in the table from which you created view. try to use the same date format in the where condition of view. – Sai Feb 15 '14 at 09:51
  • one more thing use to_date(trim(:value),'YYYY/MM/DD')" – Sai Feb 15 '14 at 09:59