SASDATEFMT= Data Set Option

Changes the SAS date format of a DBMS column.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Category:	Data Set Control
Default:	DBMS-specific
Data source:	Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Spark, Teradata, Vertica, Yellowbrick
Note:	Support for Hadoop, JDBC, and Spark was added in SAS 9.4M9.
See:	DBSASTYPE= data set option

Table of Contents

Syntax

SASDATEFMT=(DBMS-date-col-1='SAS-date-format'
<… DBMS-date-col-n='SAS-date-format'>)

Syntax Description

DBMS-date-col

specifies the name of a date column in a DBMS table.

SAS-date-format

specifies a SAS date format that has an equivalent (like-named) informat. For example, DATETIME21.2 is both a SAS format and a SAS informat, so it is a valid value for the SAS-date-format argument.

Details

If the SAS column date format does not match the date format of the corresponding DBMS column, convert the SAS date values to the appropriate DBMS date values. Use the SASDATEFMT= option to convert date values from the default SAS date format to another SAS date format that you specify.

Use the SASDATEFMT= option to prevent date type mismatches in these circumstances:

during input operations to convert DBMS date values to the correct SAS DATE, TIME, or DATETIME values
during output operations to convert SAS DATE, TIME, or DATETIME values to the correct DBMS date values.

The column names specified in this option must be DATE, DATETIME, or TIME columns; columns of any other type are ignored.

The format specified must be a valid date format; output with any other format is unpredictable.

If the SAS date format and the DBMS date format match, this option is not needed.

The default SAS date format is DBMS-specific and is determined by the data type of the DBMS column. See the DBMS-specific reference section about data types for your SAS/ACCESS interface.

Note: For non-English date types, SAS automatically converts the data to the SAS type NUMBER. SASDATEFMT= does not currently handle these date types. However, you can use a PROC SQL view to convert the DBMS data to a SAS date format as you retrieve the data or use a format statement in other contexts.

Oracle: It is recommended that you use DBSASTYPE= instead of SASDATEFMT=.

Examples

Example 1: Change the Date Format in Oracle

In this example, the APPEND procedure adds SAS data from the SASLIB.DELAY data set to the Oracle table that is accessed by MYDBLIB.INTERNAT. Using SASDATEFMT=, the default SAS format for the Oracle column DATES is changed to the DATE9. format. Data output from SASLIB.DELAY into the DATES column in MYDBLIB.INTERNAT now converts from the DATE9. format to the Oracle format assigned to that type.

 libname mydblib oracle user=myusr1 password=mypwd1;
 libname saslib 'your-SAS-library';
 proc append base=mydblib.internat(sasdatefmt=(dates='date9.'))force
    data=saslib.delay;
 run;

Example 2: Change a SAS Date Format to a Teradata Format

In the next example, SASDATEFMT= converts DATE1, a SAS DATETIME value, to a Teradata date column named DATE1.

libname x teradata user=myusr1 password=mypwd1;
proc sql noerrorstop;
   create table x.dateinfo ( date1 date );
   insert into x.dateinfo
   ( sasdatefmt=( date1='datetime21.') )
     values ( '31dec2000:01:02:30'dt );

Example 3: Change a Teradata Date Format to a SAS Format

In this example, SASDATEFMT= converts DATE1, a Teradata date column, to a SAS DATETIME type named DATE1.

libname x teradata user=myusr1 password=mypwd1;
data sas_local;
format date1 datetime21.;
set x.dateinfo( sasdatefmt=( date1='datetime21.') );
run;

Last updated: February 3, 2026