Converting Times to Strings

–INSERTING DATA INTO TEST TABLE

INSERT INTO MYTESTTABLE
VALUES(DATE ‘2023-10-6’,
TIMESTAMP ‘2017-12-02 23:20:10’);

–TEST TABLE
SELECT MYDATE
FROM MYTESTTABLE;

— USE THE TO_CHAR() TO CONVERT DATES
— HH BY ITSELF GIVES THE TIME IN FRACTION OF A 12 HOUR PERIOD OR USE HH12 TO EXPLICITY USE THAT CLOCK
— TO GET THE 24 HOUR CLOCK, YOU HAVE TO USE THE HH24 CLOCK
SELECT ‘Today is ‘ || TO_CHAR( MYDATE, ‘HH24’) AS WOW
FROM MYTESTTABLE;

–MINUTES
— THE ABBREVIATION FOR MINUTES IS MI
SELECT ‘Today is ‘ || TO_CHAR( MYDATE, ‘HH12:MI’) AS WOW
FROM MYTESTTABLE;

— SECONDS
— THE ABBREVIATION FOR SECONDS IS SS
SELECT ‘Today is ‘ || TO_CHAR( MYDATE, ‘HH12:MI:SS’) AS WOW
FROM MYTESTTABLE;

— ADD AM. AND PM.
— HERE WE ADD AM
SELECT ‘Today is ‘ || TO_CHAR( MYDATE, ‘HH12:MI:SS AM’) AS WOW
FROM MYTESTTABLE;

— LET US ADD PM TO THE TIME
SELECT ‘Today is ‘ || TO_CHAR( MYDATE, ‘HH12:MI:SS PM’) AS WOW
FROM MYTESTTABLE;

— ADD TEXT INSIDE OF THE TIME PARAMETER.
— YOU CAN SEE THAT I ADD WHIPPING TO THE TIME

SELECT ‘Today is ‘ || TO_CHAR( MYDATE, ‘HH12:MI:SS “WHIPPING” PM’) AS WOW
FROM MYTESTTABLE;

— LET US ADD FRACTION OF A SECOND
SELECT ‘Today is ‘ || TO_CHAR( MYDATE, ‘HH12:MI:SS.FF PM’) AS WOW
FROM MYTESTTABLE;

— ADD FRACTIONAL SECONDS, OR BASICALLY THE AMOUNT OF DECIMAL PLACES FOR THE SECONDS
— LET US ADD FRACTION OF A SECOND
— BELOW THE SECONDS ARE IN 100THS OF A SECOND
— IF . IS NOT USED AS A SERPARATOR FOR SECONDS, YOU CAN USE THE RADIX CHARACTER, X TO GET THE LOCAL SEPARATOR
SELECT ‘Today is ‘ || TO_CHAR( MYDATE, ‘HH12:MI:SS.FF2 PM’) AS WOW
FROM MYTESTTABLE;

SELECT ‘Today is ‘ || TO_CHAR( MYDATE, ‘HH24:MI:SSXFF2’) AS WOW
FROM MYTESTTABLE;

— KNOW THIS TIME FORMAT TO MAKE YOUR LIFE EASIER: (HH12:MI:SSXFF2)

Shortcut to Dates

-- Shorthand for time TS
-- Short form of date shortcut by adding TS to the TO_CHAR parameter
SELECT 'Today is ' || TO_CHAR(MYDATE, 'DL TS')
FROM MYTESTTABLE;

–or
— Shorthand for time TS
— Short form of date shortcut by adding TS to the TO_CHAR parameter
SELECT ‘Today is ‘ || TO_CHAR(MYDATE, ‘DS TS’)
FROM MYTESTTABLE;

— short form time TS
— Long form of date shortcut by adding TS to the TO_CHAR parameter
SELECT ‘Today is ‘ || TO_CHAR(MYDATE, ‘DL TS’)
FROM MYTESTTABLE;

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top