04-09-2012 08:55 AM
I have some date fields in an Oracle table that I am trying to filter on in Enterprise Guide 4.1. The dates come in with date and time. I want to reformat the fields (maybe create a new column) to get rid of the time portion of the date field. I don't know the syntax to do this.
The filtering on the date field goes something like this:
Eff_frm_Dt <= current date
Eff_to_Dt is missing or eff_to_Dt >= current date
For current date I have been using intnx('day',date(),0)
When I use the above filter on the date fields, I get no results.
When I remove the filtering, I get results but I need to be able to filter on the dates.
Since the date comes in as date and time, I thought I should reformat the date field to remove the time portion creating new columns. Then use those columns to filter on.
04-09-2012 09:05 AM
You can use the DATEPART() function to get just the date value from a datetime field.
However, if you are pulling this from Oracle, you might want to create your filtered subset first, based on the unaltered datetime values. Then, when the subset is in SAS, you can manipulate the datetime fields further with a second query that calculates what you need.
Why two steps? Well, not all SAS function constructs can pass down to the Oracle database. If you build a filter that is based on a SAS function (such as DATEPART or INTNX), SAS might be forced to bring *more* of the Oracle rows into SAS to perform the function and evaluate the filter. If you can express the first-pass filter in a way that Oracle can process, then you'll get a smaller result set into SAS in a more efficient way. Once the results are in SAS, SAS functions can execute very quickly without having to move a lot of records around.
04-09-2012 09:15 AM
What would be the syntax for DatePart? I am not sure I follow you on the following:
"you might want to create your filtered subset first, based on the unaltered datetime values."
I can't create the filtered subset since filtering on the dates does not work. That is the reason for my question. I thought maybe it was because the dates have a time attached. Maybe if I try to get rid of the time, I can filter on the dates.
04-09-2012 09:27 AM
To filter on some datetime values, try the literal syntax such as:
where Eff_frm_Dt BETWEEN '01MAR2012:00:00:00'dt AND '09APR2012:00:00:00'dt;
To use the DATEPART function first and apply a format at the same time, you could calculate a new column:
/* justdate */
(datepart(Eff_frm_Dt)) FORMAT=date9. AS justdate
And then build your filters based on the new column JUSTDATE.
04-09-2012 09:37 AM
Since your target variable is a DATETIME value, try creating your filter with a DATETIME value as well. Rather than use "current_date", create a variable named "current_time" set to just before midnight:
current_time = input(put(current_date,date9.) || ':23:59:59';
04-09-2012 10:07 AM
I got it to work. I created two calculated columns using DatePart. Then I formated each calculated column with a date format. Then I used those calculated columns to filter on.
(EfftoDt is missing or EfftoDt >= intnx('day',date(),0))