Date formats

[lloraine]

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. 

[ChrisHemedinger]

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.

Chris

[lloraine]

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. 

[ChrisHemedinger]

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:

SELECT Eff_frm_Dt,

          /* justdate */

            (datepart(Eff_frm_Dt)) FORMAT=date9. AS justdate

And then build your filters based on the new column JUSTDATE.

Chris

[BobD]

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';

Good luck!

[lloraine]

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.

EfffrmDt<=intnx('day',date(),0)

(EfftoDt is missing or EfftoDt >= intnx('day',date(),0))