Set or get date and time options
|
WSupported on Windows
|
USupported on Unix
|
VSupported on OpenVMS
|
NSupported in Synergy .NET
|
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)
%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.
|
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 |
|
|
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 |
|
|
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).
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:
- Adding th (which is not case sensitive) to any uppercase digit mask appends ST, ND, RD, or TH, as appropriate, to the date string at the indicated place. For lowercase digit masks, lowercase letters are appended.
- When embedding characters in a string that’s part of the mask, enclose in double quotes any characters that are valid masks. For example, if you want the word “day” as part of the format, enclose it in double quotes as in the following: ‘DDDth “day”’.
- With VTX12_ODBC (which is no longer supported), SQL Server date columns were incorrectly returned as YYYY-MM-DD (and %SSC_OPTION settings did not affect this format). To restore this behavior for VTX12_SQLNATIVE (i.e., a supported SQL Server driver), use %SSC_OPTION to set the format to YYYY-MM-DD after a SQL Server connection.
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)
