Date reading as 1960 after conversion

[Q1983]

data test;
eff_date = ............; /*this is how the database reads the eff_date.  it is a date field*/
eff_date1 = datepart(eff_date);
format eff_date1 date9.;
run;

It produces 22AUG1960 for each and every row in the data.  Is there a fix

[PaigeMiller]

If it is a date variable, and you then use the DATEPART function on it, you get incorrect results. So don't use DATEPART on date values.

By the way, this is particularly unhelpful:

eff_date = ............; /*this is how the database reads the eff_date.  it is a date field*/

Why don't you show us the actual value? 

[ballardw]

Show us your input data value, that actual eff_date value and real code.

The example that you post does not run as two . as you used them are not allowed in an assignment.

6    data test;
7    eff_date = ............; /*this is how the database reads the eff_date.  it is a date field*/
                 -
                 388
                 200
ERROR 388-185: Expecting an arithmetic operator.

ERROR 200-322: The symbol is not recognized and will be ignored.

8    eff_date1 = datepart(eff_date);
9    format eff_date1 date9.;
10   run;

Note that other databases date approaches are not the same as SAS and conversion may be needed. The SAS DATEPART function expects a SAS datetime value, which is a number of seconds from 01JAN1960:00:00:00, as input. If the value provided by the database is other than that, and most are, then the SAS date functions may not be appropriate. Also, how you connect to an external database can have an impact on this process. Some connections have the ability to convert on the file and others do not.

Are you seeing that date in SAS or the external database? The date you show corresponds to a numeric 234.

[Tom · Accepted solution]

Date values are the number of days since 1960.  So the date value for 22AUG1960 is the number 234.  

Dateteime values are the number of seconds since 1960.

The DATEPART() function converts from seconds to days. So basically it just divides by the number of seconds in a day.

3212  data _null_;
3213    date='22AUG1960'd ;
3214    datetime=dhms(date,0,0,0);
3215    put (date datetime)(=comma24.)
3216     /  (date datetime)(=date9.)
3217     /  (date datetime)(=datetime19.)
3218    ;
3219  run;

date=234 datetime=20,217,600
date=22AUG1960 datetime=*********
date=01JAN1960:00:03:54 datetime=22AUG1960:00:00:00

So you can see that you probably started with a date value close to 20,217,600.  

So I suspect that some has just stored integers into your date variable and used integers that would look to a human like a date created using digits for YYYYMMDD.

So to convert just use:

eff_date1 = input(put(eff_date,z8.),yymmdd8.);