Dates and Times in FedSQL

Overview of 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.

FedSQL Date, Time, and Datetime Constants

You write FedSQL date, time, or datetime constants using the following syntax:
DATE 'yyyy-mm-dd'
TIME 'hh:nn:ss[.fraction]'
TIMESTAMP ' yyyy-mm-dd hh:mm:ss[.fraction]'
where
  • yyyy is a four-digit year
  • mm is a two-digit month, 01–12
  • dd is a two-digit day, 01–31
  • hh is a two-digit military hour, 00–23
  • nn is a two-digit minute, 00–59
  • ss is a two-digit second, 00–61
  • fraction can be one to ten digits, 0–9, is optional, and represents a fraction of a second
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');

Using Dates and Times in SAS Data Sets, SPD Engine Data Sets, and SPD Server Tables

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:
SAS Formats Assigned to Date and Time Values in SAS Data Sets, SPD Engine Data Sets, and SPD Server Tables
FedSQL Date/Time Data Type
SAS Format
DATE
DATE9.
TIME
TIME8.
TIMESTAMP
DATETIME19.2
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:
Results for Date, Time, and Timestamp
Results for Date, Time, and Timestamp
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:
Results of Using the PUT Statement
Results of Using the PUT Statement

Date, Time, and Datetime Functions

Here is a list and description of FedSQL date, time, and datetime functions:
Date, Time, and Datetime Functions
Function
Description
CURRENT_DATE
returns the current date for your time zone.
CURRENT_TIME
returns the current time for your timezone.
CURRENT_TIME_GMT()
returns the current GMT time.
CURRENT_TIMESTAMP
returns the current date and time for your timezone.
CURRENT_TIMESTAMP_GMT()
returns the current GMT date and time.
DAY
returns the numeric day of the month from a date or datetime value.
HOUR
returns the hour from a time or datetime value.
MINUTE
returns the minute from a time or datetime value.
MONTH
returns the numeric month from a date or datetime value.
SECOND
returns the second from a time or datetime value.
YEAR
returns the year from a date or datetime value.

Date, Time, and Datetime Formats for SAS Data Sets, SPD Engine Data Sets, and SPD Server Tables

The following date, time, and datetime formats can be used to format SAS data sets, SPD Engine data sets, and SPD Server tables:
Date, Time, and Datetime Functions Arranged by Type
Type
Language Element
Input
Formatted Result
Date formats
DATE.
date'2013-03-17'
17MAR13
DATE9.
date'2013-03-17'
17MAR2013
DATEAMPM21.
timestamp'2013-03-17 14:22:21'
17MAR13:02:22:21 PM
DATEAMPM23.
timestamp'2013-03-17 14:22:21'
17MAR2013:02:22:21 PM
DAY.
date'2013-03-17'
17
DDMMYY.
date'2013-03-17'
17/03/13
DDMMYB.
date'2013-03-17'
17 03 13
DDMMYYC.
date'2013-03-17'
17:03:13
DDMMYYD10.
date'2013-03-17'
17-03-2013
DDMMYYN.
date'2013-03-17'
17032013
DDMMYYP.
date'2013-03-17'
17.03.13
DDMMYYS.
date'2013-03-17'
17/03/13
DOWNAME.
date'2013-03-17'
Monday
JULIAN.
date'2013-03-17'
13077
MMDDYY.
date'2013-03-17'
03/17/13
MMDDYY10.
date'2013-03-17'
03/17/2013
MMDDYYB.
date'2013-03-17'
03 17 13
MMDDYC.
date'2013-03-17'
03:17:13
MMDDYD.
date'2013-03-17'
03-17-13
MMDDYN.
date'2013-03-17'
03172013
MMDDYYP.
date'2013-03-17'
03.17.13
MMDDYYS.
date'2013-03-17'
03/17/13
MMYY.
date'2013-03-17'
03M2013
MMYYC.
date'2013-03-17'
03:2013
MMYYD.
date'2013-03-17'
03-2013
MMYYN.
date'2013-03-17'
032013
MMYYP.
date'2013-03-17'
03.2013
MMYYS.
date'2013-03-17'
03/2013
MONNAME.
date'2013-03-17'
March
MONTH.
date'2013-03-17'
3
MONYY.
date'2013-03-17'
MAR13
WEEKDATE.
date'2013-03-17'
Monday, March 17, 2013
WEEKDATX.
date'2013-03-17'
Monday 17 March 2013
WEEKDAY.
date'2013-03-17'
2
Datetime formats
DTDATE.
timestamp'2013-03-18 14:22:21'
17MAR13
DTMONYY.
timestamp'2013-03-18 14:22:21'
MAR13
DTWKDATX.
timestamp'2013-03-18 14:22:21'
Monday, March 17 2013
DTYEAR.
timestamp'2013-03-18 14:22:21'
2013
DTYYQC.
timestamp'2013-03-18 14:22:21'
13:1
Quarter formats
QTR.
date'2013-03-17'
1
QTRR.
date'2013-03-17'
I
Time formats
HHMM.
time'14:22:21'
14:22
HOUR.
time'14:22:21'
14
TIME.
time'14:22:21'
14:22:21
TIMEAMPM.
time'14:22:21'
2:22:21 PM
TOD.
time'14:22:21
14:22:21
Year formats
YEAR.
date'2013-03-17'
2013
YYMM.
date'2013-03-17'
2013M03
YYMMC.
date'2013-03-17'
2013:03
YYMMD.
date'2013-03-17'
2013-03
YYMMN.
date'2013-03-17'
201303
YYMMP.
date'2013-03-17'
2013.03
YYMMS.
date'2013-03-17'
2013/03
YYMMDD.
date'2013-03-17'
13-03-17
YYMMDDB.
date'2013-03-17'
13 03 17
YYMMDDC.
date'2013-03-17'
13:03:17
YYMMDDD.
date'2013-03-17'
13-03-17
YYMMDDN.
date'2013-03-17'
20130317
YYMMDDP.
date'2013-03-17'
13.03.17
YYMMDDS.
date'2013-03-17'
13/03/17
YYMON.
date'2013-03-17'
2013MAR
Year/Quarter formats
YYQ.
date'2013-03-17'
2013Q1
YYQC.
date'2013-03-17'
2013:1
YYQD.
date'2013-03-17'
2013-1
YYQN.
date'2013-03-17'
20131
YYQP.
date'2013-03-17'
2013.1
YYQS.
date'2013-03-17'
2013/1
YYQR.
date'2013-03-17'
2013QI
YYQRC.
date'2013-03-17'
2013:I
YYQRD.
date'2013-03-17'
2013-I
YYQRN.
date'2013-03-17'
2013I
YYQRP.
date'2013-03-17'
2013.I
YYQRS.
date'2013-03-17'
2013/I
Last updated: February 23, 2017