%SSC_OPTION

Set or get date and time options

WTSupported in traditional Synergy on Windows
WNSupported in Synergy .NET on Windows
USupported on UNIX
VSupported on OpenVMS
value = %SSC_OPTION(dbchannel, SSQL_SETOPT|SSQL_GETOPT, base_date, format_string, null_mask)

Return value

value

This function returns SSQL_NORMAL (success) or SSQL_FAILURE (failure). (i)

Arguments

dbchannel

An internal database channel previously initialized using %SSC_INIT and connected by %SSC_CONNECT. (n)

SSQL_SETOPT

Set the following options. (n)

SSQL_GETOPT

Get the following options. (n)

base_date

Returns or sets a value that’s used to adjust numeric dates (decimal or integer) fetched from the database. We recommend leaving base_date set to its default, which is ‑1721378. See the Discussion below for instructions. (n)

format_string

Returns or sets the date/time format string. The maximum number of characters is 64. The default mask is DD-MON-YYYY. (a)

null_mask

Returns or sets a value that is bit OR’d to the field when a column is described and nulls are allowed. This is for internal use only. Leave this option set to its default value, which is 0. (n)

Discussion

%SSC_OPTION gets or sets current date and time option settings. These settings affect all current and future connections.

To get the current date and time option settings, use SSQL_GETOPT. To set an option, do the following:

1. Call %SSC_OPTION using SSQL_GETOPT to retrieve current settings into variables.
2. Assign new values to the variables whose settings you want to change.
3. Call %SSC_OPTION using SSQL_SETOPT to update the setting for any variable whose value you changed in step 2; other options will maintain their original settings.

Base_date determines the base date for fetched dates. When a date is fetched into a numeric output variable, the date is translated into a Julian date, which is a value that represents the number of days between the returned date and the beginning of AD 1. The value of base_date is then added to or (if it’s negative) subtracted from the Julian date. (A positive value moves the base date forward to a later AD year. A negative value moves the base date back to a BC year.) We recommend that you leave base_date set to its default (‑1721378), which is compatible with the Synergy DBL routines %NDATE and %JPERIOD.

The following table lists the formatting options for date/time data. The width of the resulting data is determined by the length of the mask.

Date/Time Formatting Characters

Sequence

Description

YYYY

Four-digit year

YY

Two-digit year

RR

Two-digit year from another century—this is a sliding window format based on 20

MM

Two-digit month of year (01‑12)

MON

Three-character month (all uppercase)

mon

Three-character month (all lowercase)

Mon

Three-character month (1st character uppercase)

MONTH

Fully named month (all uppercase)

month

Fully named month (all lowercase)

Month

Fully named month (1st character uppercase)

DDD

Three-digit day of year (001‑366)

DD

Two-digit day of month (01‑31)

D

Single-digita day of week (1‑7)b

DY

Three-character day (all uppercase)

dy

Three-character day (all lowercase)

Dy

Three-character day (1st character uppercase)

DAY

Fully named day (all uppercase)

day

Fully named day (all lowercase)

Day

Fully named day (1st character uppercase)

HH12

Two-digit hour (00‑11)

HH, HH24

Two-digit hour (00‑23)

MI

Two-digit minutes (00‑59)

SS

Two-digit seconds (00‑59)

SSSSS

Seconds past midnight (00000‑86399)

J

Julian daya

Q

Single-digita quarter of year (0‑4)

UUUUUU

Microsecond (datetime only)

W

Single-digita week of the month (1‑4)b

WW

Two-digit week of the year (01‑52)

other

Delimiting character: slash (/), dash (‑), etc.

a. A single-character mask will not work if it is the only character in a format string. It will work if there are other characters (mask characters and/or non-mask characters).

b. Weeks start with Sunday.

The following table lists some example date/time values, some masks that could be applied to those values, and the results.

Retrieved date

Mask

Result

Feb 6, 1958

"DD/MM/YYYY"

“06/02/1958”

Feb 6, 1958

"qth quarter of YY"

“1st quarter of 58”

Feb 6, 1958

"YYYYMMDD"

“19580206”

Nov 1, 1995 20:48:46

"HH12:MI on Day"

“08:48 on Wednesday”

Dec 1, 1994

'DDDth "day"'

335TH day

Note the following:

Examples

The following example changes the date format mask. Note that %SSC_OPTION is called twice, as recommended.

sts = %ssc_option(dbchn, SSQL_GETOPT, date_base, date_fmt, null_mask)
date_fmt = "MM-DD-YYYY"
sts = %ssc_option(dbchn, SSQL_SETOPT, date_base, date_fmt, null_mask)