Dates and Times in FedSQL

FedSQL supports the industry standard conventions for dates, times, and datetimes using the DATE, TIME, and TIMESTAMP data types. All third-party data sources that are supported by FedSQL support these data types. You can create SAS data sets, SPD Engine data sets, and SPD Server tables using these data types, but FedSQL converts these data types to a DOUBLE having a SAS date, time, or datetime format.

FedSQL can read and process SAS dates, time, and datetime values only as values with a data type of DOUBLE. FedSQL cannot convert a SAS date, time, or datetime value with a data type of DOUBLE to a value with a data type of DATE, TIME, or TIMESTAMP.

FedSQL date, time, and datetime functions can provide local current time as well as GMT current time.

FedSQL formats enable dates, times, and datetimes to be formatted in various ways.

You write FedSQL date, time, or datetime constants using the following syntax:

where

The string portion of the value after the DATE, TIME, or TIMESTAMP keyword must be enclosed in single quotation marks.

In the date constant, the hyphens are required and the length of the date string must be at least 8. You are not required to precede single-digit dates with a zero (0).

In the time constant, the colons are required. If the fraction of a second is not present, the time string must be eight characters long and it can include or exclude the period. If the fraction of second is present, the fraction can be up to nine digits long. The time constant can be between 8 and 18 characters long.

In the timestamp constant, the hyphens in the date are required as well as the colons in the time. If the fraction of a second is not present, it can include or exclude the period. If fraction of a second is present, the fraction can be up to nine digits long. The timestamp constant can be between 19 and 29 characters long.

Here are examples of FedSQL date, time, and timestamp constants:

date'2008-01-31'
date'2008-1-1'
time'20:44:59'
timestamp'2007-02-02 07:00:00.7569'

Here is an example of creating a table that includes datetime values:

create table bikerace (name char(30), entry_number int,
   registration_date timestamp);
   insert into bikerace values ('Andersen, Mark', 342,
      timestamp'2007-03-15 12:27:33');
   insert into bikerace values ('Steinbek, Mark', 244,
      timestamp'2006-11-27 13:26:19');

When you create a table using the BASE driver or the SPD drivers, you specify columns for date, time, and datetime values by using the DATE, TIME, and TIMESTAMP data types. FedSQL converts values with these data types to a data type of DOUBLE having these SAS formats:

These SAS formats cannot be altered by using the FedSQL language ALTER TABLE statement. They can be altered only by using protocols in Base SAS to alter formats that are assigned to a column in a SAS data set, SPD Engine data set, or SPD Server table.

The following example creates a SAS data set that contains a date, time, and datetime value using the DATE, TIME, and TIMESTAMP data types, and illustrates how they are displayed using SAS formats:

create table basedt (d date, t time, ts timestamp);
insert into basedt values (date '2013-03-14', time '10:31:22',
   timestamp '2013-03-14 13:30:33.222');
select * from basedt;

Here is the output:

Any format that you might specify for these date, time, and datetime values overrides the format that is stored with these values. The following SELECT statement uses the PUT function to format the datetime value using the DATETIME21.2 format in order to display the four-character year 2008:

select put(ts,datetime21.2) from basedt;

Here is the output:

Here is a list and description of FedSQL date, time, and datetime functions:

The following date, time, and datetime formats can be used to format SAS data sets, SPD Engine data sets, and SPD Server tables: