-1

I have a question on how to compare YYYYMM in Oracle SQL. graduation_date is saved in string format like '200212'. I want to query rows with graduation_date from Jan 2007 to Jan 2010.

Here is my query:

select ids,
from   table
where  to_date(substr(graduation_date,1,6),'YYYYMM' between 'Jan-2007'and 'Jan-2010'

I got error

ORA-01858:a non-numeric character was found where a numeric was expected

Can anyone can help figure this out? Many thanks!

I found a way to compare. Since the graduation_date is saved as vchar2 format like '20021200'.Default value is '00000000'. ONLY Year & Month is saved in Graduation_date. Here is my query: select ids, from table where graduation_date between '20070100'and '20100100'

I tired some other ways advised but got ORA-01843: not a valid month error select ids, from table where to_date(substr(graduation_date,1,6),'YYYYMM' between 'Jan-2007'and 'Jan-2010'

Thank you guys but just wondering why I can't use to_date to compare in this scenario?

user8237908
  • 1
  • 6
  • Remove the comma after ids... Need a closing bracket after YYYYMM, is graduation_date of type char/varchar? If not then you can't do substr... Finally do to_date('200701', 'YYYYMM') and to other date also... – deepakl Aug 04 '17 at 16:59
  • A date literal is written like this: `date '2007-01-01'`. Not `'Jan-2007'`. – William Robertson Aug 05 '17 at 09:56

2 Answers2

0

Remove the comma after ids... Need a closing bracket after YYYYMM, is graduation_date of type char/varchar? If not then you can't do substr... Finally do to_date('200701', 'YYYYMM') and to other date also...

deepakl
  • 172
  • 8
  • my where clause is where To_date(substr(d.graduation_date,1,6),'YYYYMM') between to_date('200701','YYYYMM') and to_date('201001','YYYYMM') but got not a valid month error – user8237908 Aug 04 '17 at 17:22
  • Outside of your current SQL, try to fire just substr part of the sql and check if you are getting the result in YYYYMM format – deepakl Aug 04 '17 at 17:32
  • the substr result is like 199908 and to_date result is lie 8/1/1999...so confused – user8237908 Aug 04 '17 at 18:00
0

In Addition to delete comma here: select ids, and to add bracket after 'YYYYMM' here: To_date(substr(graduation_date,1,6),'YYYYMM' you also need to know:

  1. You are seeing the dates in the format which is set in your NLS settings. Even after conversion you letting your NLS to show in already set format, in your case it's seems like MM/DD/YYYY, check by:

    SELECT VALUE FROM nls_session_parameters WHERE parameter = NLS_DATE_FORMAT';

if you want to select date in your special format then after to_date function also use to_char function, like (if your data is in that graduation_date column like YYYYMM then no need to use substr function, else use as below):

SELECT to_char (to_date (SUBSTR (graduation_date, 1, 6), 'YYYYMM'), 'YYYYMM') FROM table;

  1. So, if you want to select your data, then use below script (if your data is in that graduation_date column like YYYYMM then no need to use substr function, else use as below):

    SELECT ids FROM table WHERE to_date (SUBSTR (graduation_date, 1, 6), 'YYYYMM') BETWEEN to_date ('200701', 'YYYYMM') AND to_date ('201001', 'YYYYMM');

  2. I don't suggest to use 'Jan-2012' (even if you convert by to_char function) with BETWEEN cause it will compare bytes of letter, not the month, so it's possible to get also June or July for each year if you use like this (and data only for Jan, not all months between them):

WHERE to_char (to_date (SUBSTR (graduation_date, 1, 6), 'YYYYMM'), 'Mon-YYYY') BETWEEN 'Jan-2007' AND 'Jan-2010';

result something like this:

Jan-2007 Jun-2007 Jul-2007 Jan-2010 ...

Ikrom
  • 474
  • 4
  • 10
  • useful links: https://stackoverflow.com/questions/5966274/how-to-handle-to-date-exceptions-in-a-select-statment-to-ignore-those-rows ; https://stackoverflow.com/questions/8008693/oracle-to-date-incorrect-output?rq=1 – Ikrom Aug 04 '17 at 21:26
  • I got ORA-01843: not a valid month error for following query SELECT ids FROM table WHERE to_date (SUBSTR (graduation_date, 1, 6), 'YYYYMM') BETWEEN to_date ('200701', 'YYYYMM') AND to_date ('201001', 'YYYYMM'); – user8237908 Aug 07 '17 at 17:54
  • So, for just make sure: grad_date is `varchar2` and data there is like `200701`, right? – Ikrom Aug 07 '17 at 18:37
  • grad_date is varchar2, default '00000000', like '19990800'. since only year and month saved, last 2 digits always 00. if I query select to_date(SUBSTR (d.grad_dt, 1, 6),'YYYYMM') from table I got result like '8/1/1999'. but if want compare the dates, like SELECT ids FROM table WHERE to_date (SUBSTR (graduation_date, 1, 6), 'YYYYMM') BETWEEN to_date ('200701', 'YYYYMM') AND to_date ('201001', 'YYYYMM') I got error msg"NOT valid month" – user8237908 Aug 07 '17 at 19:38
  • I found a way : select ids, from table where graduation_date between '20070100'and '20100100' then I can get results. but just confused why to_date is not working here? – user8237908 Aug 07 '17 at 19:40
  • try to do like this: `SELECT ids FROM table WHERE to_date (SUBSTR (graduation_date, 1, 6), 'YYYYMM') BETWEEN to_date ('200701', 'YYYYMM') AND to_date ('200702', 'YYYYMM');` I'm afraid that maybe you have some data like: `201220` with not valid month... – Ikrom Aug 07 '17 at 19:48
  • I found the reason. There were some bad data and cause the problem. – user8237908 Aug 08 '17 at 13:06
  • Yes, I meant this. If my suggestions worked for then please Vote and set the Green flag for the answer. – Ikrom Aug 08 '17 at 14:44