DATETIME20 Format error on where clause

[#1]

Hi,

When I run my SAS code with this where clause, my SAS errors 

AND XICQ_SUBACCOUNT_SUMMARY.XICQ_COMPLETION_DATE>={ts '2013-01-01 00:00:01'}

The variable format XICQ_COMPLETION_DATE DATETIME20. and the output is like 

13AUG2004:00:00:00

21MAY2004:00:00:00

16AUG2004:00:00:00

03JUN2004:00:00:00

06MAY2004:00:00:00

Any idea how I can get this part of my where clause to work please?

Thanks

[andreas_lds]

using a proepr datetime constant will most likely solve the issue, but because you have not posted the error message, this is just guessed.

[Kurt_Bremser]

{ts '2013-01-01 00:00:01'}

is not a proper SAS datetime literal.

The correct SAS notation would be

'01jan2013:00:00:01'dt

or you could use

input('2013-01-01T00:00:01',e8601dt.)

instead

[#4]

This is my error message - 

ERROR: ORACLE prepare error: ORA-00911: invalid character. SQL statement: SELECT ACCOUNTS.ACC_ACCOUNT_NO,
ACCOUNTS.ACC_PURPOSE_CODE, ACCOUNT_TYPES.ACT_LEVEL1_CODE, XICQ_SUBACCOUNT_SUMMARY.XICQ_COMPLETION_DATE, MAM34.CLIINCAMT,
MAM34.CLIINCFREQ, ACCOUNTS.ACC_ACTUAL_ADVANCE, MAM34.CLIINCTYP, MAM34.CLIINCSQ, MAM34.APPLSEQ, ACCOUNTS.ACC_SUBACC_NO,
MAM34.CLINUM FROM ACCOUNTS LEFT OUTER JOIN ACCOUNT_TYPES ON ACCOUNTS.ACC_ACT_CODE=ACCOUNT_TYPES.ACT_CODE LEFT OUTER JOIN
XICQ_SUBACCOUNT_SUMMARY ON (ACCOUNTS.ACC_ACCOUNT_NO=XICQ_SUBACCOUNT_SUMMARY.XICQ_ACCOUNT_NO) AND
(ACC_SUBACC_NO=XICQ_SUBACCOUNT_SUMMARY.XICQ_SUBACC_NO) LEFT OUTER JOIN MAM34 ON
XICQ_SUBACCOUNT_SUMMARY.XICQ_ACCOUNT_NO=MAM34.MORTGAGE WHERE MAM34.CLIINCTYP='REN' AND ACCOUNT_TYPES.ACT_LEVEL1_CODE='7' AND
ACCOUNTS.ACC_PURPOSE_CODE<>'FAD' AND XICQ_SUBACCOUNT_SUMMARY.XICQ_COMPLETION_DATE>={ts '2013-01-01 00:00:01'}.

I tried using these ut still didn't work

'01jan2013:00:00:01'dt

or you could use

input('2013-01-01T00:00:01',e8601dt.)

[Kurt_Bremser]

So you have an Oracle problem, not a SAS problem. Ask your Oracle people for assistance how to provide a timestamp literal, or consult the Oracle documentation.

I guess you should use

TIMESTAMP '2013-01-01 00:00:01'

instead of

{ts '2013-01-01 00:00:01'}

[Sven111]

You need to be careful with DATE/TIMESTAMP's when moving between Oracle and SAS.  I don't recall exactly which off the top of my head, but I know that when moving between the two SAS interprets Oracle DATES or TIMESTAMPS as either SAS DATETIMES or CHAR's, I think there is also a difference between TIMESTAMP and TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE, in whether SAS will treat it as a DATETIME or CHAR.  Also, Oracle does not have a concept of a pure date, even the DATE type includes a time component.

My guess is that this type mismatch is causing your problems.