Changing Date Format in Proc SQL

[S0MBR0]

Hello,

I have a table that displays dates in the format "01JAN2020:12:00:00" and I'm trying to pull that data into a newly created table using proc sql without the time.  I've done a bit of research on this and can't seem to get the syntax correct (using SELECT CONVERT) and an error when trying to run the query in SAS Enterprise Guide 7.1.  Any ideas on how I might be able get rid of time?

Thanks!

[novinosrin]

HI @S0MBR0  Assuming your date is a numeric SAS Datetime value displaying the value using the format Datetime. , the values would of course look like "01JAN2020:12:00:00"

Should you require a Date value from a datetime value, You would need to use Datepart function to extract the date value. 

select datepart(datetime_var) as new_date format=whatever date format 

[PaigeMiller · Accepted solution]

Use format DATETIME7. or DATETIME9.

Please note this is not a DATE format as your title and text imply. These are date/time formats which are not the same as DATE formats and should not be confused with DATE formats.

Date/Time formats must be used when your variable is a date/time variable, which your variable is (obviously). Date formats cannot be used on date/time variables, and vice versa.

Here is a list of all date, datetime and time formats in alphabetical order, each one clearly states whether it is a date format or a datetime format or a time format. https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=allprodslang&docsetTarget=syn...

[S0MBR0]

@gpai - This worked perfectly, thank you so much!  I also appreciate the link you provided, it will be a good resource for me going forward.

[Kurt_Bremser]

In SAS (and most database systems), there is a fundamental difference between dates and times/datetimes.

Dates are stored as counts of days (allowing the use of only 4 numeric bytes), while times and datetimes are stored as counts of seconds (in the full 8-byte real format). That's why you need a special format for displaying only the date part of a datetime, or the datepart() function to extract the date out of a datetime value.

[S0MBR0]

@Kurt_Bremser - Thank you for the information, I did not fully grasp the difference between Date and DateTime but your explanation makes a lot of sense.