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