TABLE 55. Converting Datetime Expression with Format String to String
|
Database
|
Syntax
|
Description
|
|
Oracle
|
|
Converts date of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE data type to a value of VARCHAR2 data type in the format specified by the date format fmt.
If fmt is omit, then date is converted to a VARCHAR2 value as follows:
� DATE is converted to a value in the default date format.
� TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE are converted to values in the default timestamp format.
� TIMESTAMP WITH TIME ZONE is converted to a value in the default timestamp with time zone format.
The 'nlsparams' specifies the language in which month and day names and abbreviations are returned. This argument can have this form:
'NLS_DATE_LANGUAGE = language'
A date format model (fmt) is composed of one or more datetime format elements as listed in following:
|
|
Element
|
Description
|
|
-
/
,
.
:
;
"text"
|
Punctuation and quoted text which is reproduced in the result.
|
|
AD
A.D.
|
AD indicator with or without periods.
|
|
AM
A.M.
|
Meridian indicator with or without periods.
|
|
|
|
BC
B.C.
|
BC indicator with or without periods.
|
|
CC
SCC
|
One greater than the first two digits of a four-digit year; "S" prefixes BC dates with "-".
For example, '20' from '1900'.
|
|
D
|
Day of week (1 - 7).
|
|
DAY
|
Name of day, padded with blanks to length of 9 characters.
|
|
DD
|
Day of month (1 - 31).
|
|
DDD
|
Day of year (0 - 366).
|
|
DY
|
Abbreviated name of day.
|
|
FF [1..9]
|
Fractional seconds; no radix character is printed (use the X format element to add the radix character). Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned.
|
|
HH
|
Hour of day (1 - 12).
|
|
HH24
|
Hour of day (0 - 23).
|
|
IW
|
Week of year (1-52 or 1-53) based on the ISO standard.
|
|
IYY
IY
I
|
Last 3, 2, or 1 digit(s) of ISO year.
|
|
IYYY
|
4-digit year based on the ISO standard.
|
|
J
|
Julian day; the number of days since January 1, 4712 BC.
|
|
MI
|
Minute (0 - 59).
|
|
MM
|
Month (01 - 12; Jan - 01).
|
|
MON
|
Abbreviated name of month.
|
|
|
|
MONTH
|
Name of month, padded with blanks to length if 9 characters.
|
|
Q
|
Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
|
|
RM
|
Roman numeral month (I - XII; Jan - I).
|
|
RR
|
Given a year with 2 digits:
� If the year is <50 and the last 2 digits of the current year are >=50, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
� If the year is >=50 and the last 2 digits of the current year are <50, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
|
|
RRRR
|
Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don't want this functionality, then simply enter the 4-digit year.
|
|
SS
|
Second (0 - 59).
|
|
SSSSS
|
Seconds past midnight (0 - 86399).
|
|
|
|
TZD
|
Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR.
|
|
TZH
|
Time zone hour.
|
|
TZM
|
Time zone minute.
|
|
TZR
|
Time zone region information.
|
|
WW
|
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
|
|
W
|
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
|
|
X
|
Local radix character.
|
|
Y
YYY
|
Year with comma in the position.
|
|
YYYY
SYYYY
|
4-digit year; "S' prefixes BC dates with "_".
|
|
YEAR
SYEAR
|
Year, spelled out; "S" prefixes BC dates with "-".
|
|
YYY
YY
Y
|
Last 3, 2, or 1 digit(s) of year.
|
|
These characters appear in the return value in the same location as they appear in the format model.
The total length of a date format model cannot exceed 22 characters. Character literals, enclosed in double quotation marks.
|
|
IBM DB2
|
8
|
|
Converts timestamp_exp to a string in the format specified by the fmt-string.
timestamp-exp - is an expression that results in a timestamp. The argument must be a timestamp or a string representation of a timestamp that is neither a CLOB nor a LONG VARCHAR. The string expression returns a CHAR or a VARCHAR value whose maximum length is not greater than 254.
fmt-string - is a character constant that contains a template for how the result is to be formatted. The length of the format string must not be greater than 254. The content of format-string can be specified only in full case.
Format string must be following:
'YYYY-MM-DD HH24:MI:SS'
where YYYY represents a 4-digit year value; MM represents a 2-digit month value (01-12; January=01); DD represents a 2-digit day of the month value (01-31); HH24 represents a 2-digit hour of the day value (00-24; If the hour is 24, the minutes and seconds values are zero.); MI represents a 2-digit minute value (00-59); and SS represents a 2-digit seconds value (00-59).
The result of the function is a varying-length character string containing a formatted timestamp expression.
In order to choose from a timestamp-exp some part (such as SECOND, MINUTE and etc.) is used accordingly SECOND, MINUTE and etc. functions. For example: if it is necessary to choose the second part from timestamp-exp then SECOND functions must be used.
|
|
|
|
|
Returns a character representation of a timestamp that has been formatted using a character template.
TO_CHAR is a synonym for VARCHAR_FORMAT.
|
|
|
<8
|
|
Converts datetime_exp of DATE, TIME and TIMESTAMP data type to a string. If the datetime_exp is null, the result is the null value.
If datetime-exp is an expression of:
� DATE data type then the length of the result is 10.
� TIME data type then the length of the result is 8.
� TIMESTAMP data type then the length of the result is 26.
A datetime_exp format model can be implemented with expressions of MINUTE, SECOND and etc. functions.
For concatenate result of MINUTE function with result of SECOND function it is necessary to convert result of these functions to string, as MINUTE and SECOND functions return integer. "||" in IBM DB2 allows concatenate only two strings. Example of this concatenate is following: CHAR( MINUTE( CURRENT TIMESTAMP)) || CHAR( SECOND( CURRENT TIMESTAMP)).
|
|
MySQL
|
|
Converts date to a string in the format specified by the date format fmt_str.
Formats the date value according to the format string (fmt_str). The following specifiers may be used in the format string:
|
|
Specifier
|
Description
|
|
%a
|
Abbreviated weekday name (Sun..Sat)
|
|
%b
|
Abbreviated month name (Jan..Dec)
|
|
%D
|
Day of the month with English suffix (0th, 1st, 2nd, 3rd, etc.
|
|
%d
%e
|
Day of the month, numeric (00..31)
|
|
%j
|
Day of year (001..366)
|
|
%m
%c
|
Month, numeric (00..12)
|
|
%M
|
Month name (January..December)
|
|
%f
|
Microseconds (000000..999999)
|
|
%i
|
Minutes, numeric (00..59)
|
|
%h
%I
%l
|
Hour (01..12)
|
|
%H
%k
|
Hour (00..23)
|
|
%p
|
AM or PM
|
|
%r
|
Time, 12-hour (hh:mm:ss followed by AM or PM)
|
|
%S
%s
|
Seconds (00..59)
|
|
%T
|
Time, 24-hour (hh:mm:ss)
|
|
%U
|
Week (00..53), where Sunday is the first day of week
|
|
%u
|
Week (00..53), where Monday is the first day of week
|
|
|
|
|
|
|
|
|
|
%V
|
Week (01..53), where Sunday is the first day of week, used with %X
|
|
%v
|
Week (01..53), where Monday is the first day of week, used with %x
|
|
%W
|
Weekday name (Sunday..Saturday)
|
|
%w
|
Day of the week (0=Sunday .. 6=Saturday)
|
|
%X
|
Year for the week, where Sunday is the first day of the week, numeric 4 digits; used with %V
|
|
%x
|
Year for the week, where Monday is the first day of the week, numeric 4 digits; used with %v
|
|
%Y
|
Year, numeric, 4 digits
|
|
%y
|
Year, numeric, 2 digits
|
|
%%
|
A literal `%'
|
