How to correctly convert from datetime to date?

[chris_nguyen]

I'm having trouble converting from a DATETIME20. format to a MMDDYY10. format. Whenever I try to to edit my table with PROC SQL code (or equivalent PROC DATASETS code) such as the one given here:

proc sql;
alter table table1
modify datetime_to_date format=mmddyy10.;
quit;

All I get is a column full of '**********'s. I can convert to a numeric date format and can convert back to DATETIME20. all right but how can I permanently change the format to MMDDYY10. format correctly?

[Reeza]

You need to do a calculation. A datetime is the number of seconds from Jan 1, 1960, while a date variable is the number of days. 

To do the conversion you can use the datepart() function in a proc sql or data step, but not in proc datasets or via an alter table.

If you just want to change the display format of your data you can use DTDATE9. or a format in that family. Unfortunately I don't think there's one that matches the format you want though you can always create your own if you'd like, see Picture formats.