topic Re: ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string. in SAS Programming

ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string.

skallamp — Wed, 29 Jul 2015 19:41:58 GMT

I am getting ORA-01830 error while running the below code. I have defined the Yearfrom and Yearthru in my macro. I tried inputting my date with single quote and without it. But same error.

Does anyone know what it is? Any insights would be helpful.

%let yearmoFrom = '20150601';

%let yearmoThru = '20150630';

Proc SQL;

Create table one as

select a, b, c,d

from data

where EFF_DT <= to_date(&yearmoThru. || '15','yyyymmdd')

and elg.MBR_TRMNTN_DT >= to_date(&yearmoFrom. || '15','yyyymmdd')

;

quit;

Re: ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string.

ballardw — Wed, 29 Jul 2015 20:37:40 GMT

this code: &yearmoThru. || '15' looks like would be attempting to generate 2015063015 (ignoring all quotes)

This does not look like any match to yyymmdd

%let yearmoThru = 201506;

for the same value should generate 20150615, which makes a bit more sense.

Re: ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string.

Tom — Wed, 29 Jul 2015 22:21:44 GMT

Since you have written SAS code (not ORACLE code) you cannot use TO_DATE() function.

Let's assume that you actually submitted that code to ORACLE in a pass thru SQL query.

Since your macro variables already contain 8 digits (20150601) when you are append two more ( || '15') you will have string that is 10 characters long. But your format string ('yyymmdd') is only 8 characters long. That is why you are getting the error message.

Either remove the || '15' or assign only 6 digits to the macro variable.

Re: ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string.

jakarman — Thu, 30 Jul 2015 06:11:16 GMT

Check the generate Oracle SQL. You are getting an Oracle error. TO_Date is an SAS function an a Oracle function it should be translated

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Seventh Edition There is a warning associated with that.

A more common pitfall is that Oracle is only knowing data-time approaches not the segregated date as SAS does.TO_DATE (Oracle)

Re: ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string.

Patrick — Thu, 30 Jul 2015 07:49:08 GMT

@Jaap

I wanted to write "to_date()" is not a SAS function. But then I checked just in case and actually the function exists as part of DS2 syntax.

BUT: to_date() is not a valid SAS function outside of DS2 so you can't use it with SAS SQL flavor.

Re: ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string.

RW9 — Thu, 30 Jul 2015 08:55:25 GMT

Hi,

I thin you need to clarify your post. Exactly where are you running this code. If it is SAS SQL, then how are you getting an oracle error? TO_DATE() is a valid Oracle function as described: http://www.techonthenet.com/oracle/functions/to_date.php

However to use that function, you need to pass your code through to the Oracle SQL parser (note you also need to place the macro variable in quotes so it gets resolved) - Note I assume the macro variable to look like 201504:

proc sql;

connect to oracle (path=...);

create table ONE as

select * from connection to oracle (

select A,B,C,D

from DATA

where EFF_DT <= to_date("&yearmoThru." || '15','yyyymmdd')

and elg.MBR_TRMNTN_DT >= to_date("&yearmoFrom." || '15','yyyymmdd'));

disconnect from oracle;

quit;

Re: ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string.

Patrick — Thu, 30 Jul 2015 09:42:29 GMT

I believe @Tom is "spot on". You must be using Oracle pass-through SQL as else we wouldn't get this kind of Oracle error back. The way you populate the macro var and the format picture I believe the following should work (in pass-through SQL):

to_date(&yearmoThru.,'yyyymmdd')

Re: ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string.

jakarman — Thu, 30 Jul 2015 09:59:39 GMT

Patrick, you are right the to_date fucntion is no sas function.

Sorry I forgot to check that one, you got me on that one. I got my head into DS2 but the strict language checkng I prefer to leave to the compiler not my head.

Rw9 made the clarification for that. (Sql pass through). His reference to the oracle to_date function.

Some alerts:

- The double quotes is not valid withn ansi SQL for strings. The double quoates are for names using enhanced lterals and strings are only processed wiht singel quotes.

That is mostly complicating the macro-var coding. Adding single quotes as one char is done with coding (a macro quoting challenge): %nrstr(%')

- The concatenation of strings OP original post, is something that looks from the sas-datastep processing. That will not work in sas-macro processing and not work in Oracle SQL.