SQL Commands: TO_TIME

Published on March 3, 2022 by Kevin Graham

The TO_TIME function converts a string in a given format to a Kognitio TIME data type. It will also accept a number
instead of a string, within certain limits. It is possible to specify a literal string, a literal number, or a
database column containing a string or number. In every case but one, their format must correspond to that which is
described by a supplied format-string. Only if the string is in the format ‘HH:MM:SS’ can the format-string be left
out.

Usage

TO_TIME(datetime-string)
TO_TIME(datetime-string, format-string)

Notes

The following date-time formats are used with TO_TIME.

Format	Description	Example
MM	Number of month	11
RM	Roman numeral month	XI
MON	Three letter month abbreviation	NOV
Mon	Same as MON, but with initial capital	Nov
mon	Same as MON, but all lower case	nov
MONTH	Month fully spelled out	NOVEMBER
Month	Same as MONTH, but with initial capital	November
month	Same as MONTH, but all lower case	november
DDD	Number of the day in the year	312
DD	Number of the day in the month	7
D	Number of the day in the week	5
DY	Three letter abbreviation of day	SUN
Dy	Same as DY but with initial capital	Sun
dy	Same as DY, but all lowercase	sun
DAY	Day fully spelled out	SUNDAY
Day	Day with initial capital	Sunday
day	Day all in lowercase	sunday
YYYY	Full four-digit year	1960
SYYYY	Signed year if BC
IYYY	ISO four-digit standard year
YYY	Last three digits of year
IYY	Last three digits of ISO year
YY	Last two digits of year
IY	Last two digits of ISO year
Y	Last digit of year
I	Last digit of ISO year
RR	Given a year with 2 digits, returns a year in the next century if the year is <50 and the last 2 digits of the current year are >=50; returns a year in the century if the year is >=50 and the last 2 digits of the current year are <50.
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, simply enter the 4-digit year.
YEAR	Year spelled out	NINETEEN-SIXTY
Year	Same as YEAR, but with initial capitals	Nineteen-Sixty
year	Same as YEAR, but in lowercase	nineteen-sixty
Q	Number of quarter	4
WW	Number of week in year	45
W	Number of week in month	1
IW	Week of year from ISO standard
J	“Julian” – days since Dec 31, 4713 B.C.
HH	Hour of day, always 1-12	11
HH12	Same as HH
HH24	Hour of day, 24-hour clock	17
MI	Minute of hour
SS	Second of minute
SSSSS	Seconds since midnight, always 0–86399
/,-:.	Punctuation to be incorporated in display for TO_CHAR, or ignored in format for TO_DATE
A.M.	Display A.M. or P.M. depending on time of day
a.m.	Same as A.M., but lowercase
P.M.	Same as A.M.
p.m.	Same as a.m.
AM	Same as A.M., but without periods
am	Same as a.m., but without periods
PM	Same as P.M., but without periods
pm	Same as p.m., but without periods
CC	Century
SCC	Same as CC, but prefixes BC with “-“
B.C.	Displays B.C. or A.D. depending on date
A.D.	Same as B.C.
b.c.	Same as B.C., but lowercase
a.d.	Same as A.D., but lowercase
BC	Same as B.C., but without periods
AD	Same as A.D., but without periods
bc	Same as b.c., but without periods
ad	Same as a.d., but without periods

Examples

The following examples all return a TIME data type with the value 14:02:02:

SELECT TO_TIME('02 P.M. 02 02', 'hhA.M. mi ss');

SELECT TO_TIME('14:02:02');

SELECT TO_TIME('50522', 'SSSSS');

Stop leaving money on the table.

Speed up your WordPress site today by moving to WP Bolt.

Get Started