Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

Date Literals

Date literals specify a day, month, and year. By default, FairCom DB SQL supports any of the following formats, enclosed in single quotation marks ( ' ).

Syntax

date-literal ::

{d 'yyyy-mm-dd'}

| mm-dd-yyyy

| mm/dd/yyyy

| yyyy-mm-dd

| yyyy/mm/dd

| dd-mon-yyyy

| dd/mon/yyyy

Arguments

{d 'yyyy-mm-dd'}

A date literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase d. End the literal with a close brace. For example:

INSERT INTO DTEST VALUES ({d '1994-05-07'})

If you use the ODBC escape clause, you must specify the date using the format yyyy-mm-dd.

dd

The day of month as a 1- or 2-digit number (in the range 01-31).

mm

The month value as a 1- or 2-digit number (in the range 01-12).

mon

The first three characters of the name of the month (in the range ‘JAN’ to ‘DEC’).

yyyy

The year as four-digit number. By default, FairCom DB SQL generates an Invalid date string error if the year is specified as anything other than digits.

Examples

The following FairCom DB SQL examples demonstrate some supported formats for date literals:

CREATE TABLE T2 (C1 DATE, C2 TIME);

INSERT INTO T2 (C1) VALUES('5/7/56');

INSERT INTO T2 (C1) VALUES('7/MAY/1956');

INSERT INTO T2 (C1) VALUES('1956/05/07');

INSERT INTO T2 (C1) VALUES({d '1956-05-07'});

INSERT INTO T2 (C1) VALUES('29-sEP-1952');

SELECT C1 FROM T2;

c1

1956-05-07

1956-05-07

1956-05-07

1956-05-07

1952-09-29

TOCIndex