Reading Oracle Date Issue

[deleted_user]

Hi,
I have a requirement to read oracle date. From the source table i need to filter the data on a Date basis. In the proc sql I need to write a where clause like 'compensationdate>='1-Mar-2010' and compensationdate<='31-Mar-2010' '.

Here I converted the date to SAS date and wrote the condition like

'compensationdate>='1-Mar-2010'd and compensationdate<='31-Mar-2010'd.

But I have a reuirement to decide the second date dynamically. For the purpose I used the intnx('month','1-Mar-2010'd,0,'E') which supposed to return a SAS date equivalent to '31-Mar-2010'd. It returns it correctly, the numeric equivalent of '31-Mar-2010'd.

But the condition doesnt work fine.
compensationdate>='1-Mar-2010'd and compensationdate<='31-Mar-2010'd is working fine. But compensationdate>='1-Mar-2010'd and compensationdate<=intnx('month','1-Mar-2010'd,0,'E') is not working fine.

Is not '31-Mar-2010'd equal to intnx('month','1-Mar-2010'd,0,'E') ???

Whate ever the function we are using which rerturns a SAS date coudn't be compared with an oracle date.

Please help me.

Thanks in advance.

[Bill]

It looks like you are confusing Oracle date syntax and SAS date syntax.

For the Oracle extract use where OracleDate>='01-mar-2010'. Once/If you have the data in a SAS dataset, then use SASDate >= '01mar2010'd

[sbb]

Suggested Google advanced search argument, this topic / post:

proc sql oracle date site:sas.com


Scott Barry
SBBWorks, Inc.

[deleted_user]

my requirement is to filter the data like compensationdate>='1-Mar-2010'd and compensationdate<='31-Mar-2010'd. It works fine if we write the condition like this. But when I use sas function which returns dates doesnt work here. Eg:compensationdate<=intnx('month','31-Mar-2010'd,0,'E'); No error but no data. Seems like '31-Mar-2010'd is not equal to intnx('month','31-Mar-2010'd,0,'E'). My source is oracle.

[deleted_user]

my requirement is to filter the data like compensationdate>='1-Mar-2010'd and compensationdate<='31-Mar-2010'd. It works fine if we write the condition like this. But when I use sas function which returns dates doesnt work here. Eg:compensationdate<=intnx('month','31-Mar-2010'd,0,'E'); No error but no data. Seems like '31-Mar-2010'd is not equal to intnx('month','31-Mar-2010'd,0,'E'). My source is oracle.

[NN]

You could try This TO_DATE(COMPENSATIONDATE)>= '01-Mar-10'
Might just work for u.

[Cynthia_sas]

Hi:
When you try to post code that contains < or <= or > or >=, the forum posting mechanism "thinks" you are trying to use an HTML tag and sometimes it will truncate your post while it looks for a valid HTML tag to go with the < and >.

So, when you post code, you either
1) have to edit the code in Notepad ahead of time to replace < with &lt; and to replace > with &gt; OR
2) you have to substitute LT for < and LE for <= and GT for > and GE for >=

There are some other nuggest of information on posting special characters and code to the forum here:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

cynthia