Using TO_CHAR to format timestamps

I use the SQL scalar function TO_CHAR all of the time to format numbers, inserting thousand separator characters. I can also use it for formatting timestamps.

In the following examples I am going to show how easy this, and it appears to duplicate a lot of the functionality of the EXTRACT scalar function.

In these examples I am going to show what I can do with a timestamp column, TIMESTAMP1, in a SQL DDL table, TESTTABLE.

Let me show you some examples of what I can do.

01 SELECT TIMESTAMP1, 02 TO_CHAR(TIMESTAMP1) AS "TO_CHAR-ed" 03 FROM TESTTABLE

In this first example I have used the TO_CHAR, on line 2, with no parameters. These results, below, show that all that has happened is that the timestamp has been converted to a variable length character value, VARCHAR.

TIMESTAMP1 TO_CHAR-ed -------------------------- -------------------------- 2023-05-10 19:14:28.404510 2023-05-10-19.14.28.404510

In this next example I am going to perform some "simple" editing.

01 SELECT TIMESTAMP1, 02 TO_CHAR(TIMESTAMP1,'YYYY-MM-DD HH.MI.SS') 03 AS "TO_CHAR-ed" 04 FROM TESTTABLE

Line 2: I want the date and the time, hours, minutes, and seconds, without the microseconds.

The time in the column I created with the TO_CHAR is different from the value in the original column, I mean more than it does not have the microseconds. The by using HH to denote the hour it returns the hour in 12 hour format, i.e. 19 hours has been translated to 7.

TIMESTAMP1 TO_CHAR-ed -------------------------- -------------------------- 2023-05-10 19:14:28.404510 2023-05-10 07.05.28

IMHO if I am going to give the 12 hour version of the hour then I need to display whether it is AM or PM.

01 SELECT TO_CHAR(TIMESTAMP1,'YYYY-MM-DD HH12.MI.SS AM') AS "12hr", 02 TO_CHAR(TIMESTAMP1,'YYYY-MM-DD HH24.MI.SS AM') AS "24hr" 03 FROM TESTTABLE

Line 1: The code HH12 and HH do the same thing, return the hour in 12 hour format. I have also added AM too. This will return either AM or PM depending upon the time. If I so wanted I could have used A.M. to return A.M. and P.M. instead.

Line 2: I include the AM in this line just to show it is independent of the hour type. In the real world why would I need "PM" to tell me that 19 hours is in the afternoon?

The results are:

12hr 24hr ---------------------- ---------------------- 2023-05-10 07.05.28 PM 2023-05-10 19.05.28 PM

If I wanted to show a timestamp in an interface that would be used by a user I would want to show the date in the month/day/year format used in the USA, and the hours and minutes of the time including part of the day. Again I can retrieve this using TO_CHAR.

01 SELECT TO_CHAR(TIMESTAMP1,'MM/DD/YY HH12:MI AM') 02 FROM TESTTABLE

Line 1: If I wanted the date in another format, such as the European day/month/year format I would use: DD/MM/YY

Personally I prefer using the HH12 to HH as it avoid confusion which type of hour is returned.

The result is as expected:

00001 ---------------------- 05/10/23 07:05 PM

Let me start showing some of the cool things we can return when using TO_CHAR. How about the name of the day of the week?

01 SELECT TO_CHAR(TIMESTAMP1,'DAY MM/DD/YY HH12:MI AM'), 02 TO_CHAR(TIMESTAMP1,'Day MM/DD/YY HH12:MI AM') 03 FROM TESTTABLE

Line 1: The addition to the TO_CHAR shown previously is the word DAY, this will return the name of the day of the week in upper case.

Line 1: Day returns the name of the day in titlecase.

This returns the following:

00001 00002 --------------------------- --------------------------- WEDNESDAY 05/10/23 07:05 PM Wednesday 05/10/23 07:05 PM

How about I want the date in words?

01 SELECT TO_CHAR(TIMESTAMP1,'Day Month DD, YYYY') 02 FROM TESTTABLE

In this statement Day returns the name of the day in titlecase, and Month returns the name of the month also in titlecase. If I wanted those only in upper case I would have used DAY and MONTH.

00001 --------------------------- Wednesday May 10, 2023

Next example, how about I want the date in what I know as Julian format, year followed by the number of the day in the year.

01 SELECT TO_CHAR(TIMESTAMP1,'YYYY-DDD'), 02 FROM TESTTABLE

Line 1: The letters DDD returns the day of the year.

This gives me:

00001 --------------------------- 2023-130

In my final statement I am going to return other interesting parts from the timestamp.

01 SELECT TO_CHAR(TIMESTAMP1,'Dy') AS "Day", 02 TO_CHAR(TIMESTAMP1,'DY') AS "DAY", 03 TO_CHAR(TIMESTAMP1,'D') AS "DoW", 04 TO_CHAR(TIMESTAMP1,'ID') AS "ISO DoW", 05 TO_CHAR(TIMESTAMP1,'IW') AS "ISO WoY", 06 TO_CHAR(TIMESTAMP1,'Q') AS "Qtr" 07 FROM TESTTABLE ;

Line 1: Dy returns the name of the day as Sun – Sat.

Line 2: DY returns the upper case name of the day, SUN – SAT.

Line 3: D returns a number for the day of the week, 1 = Sunday.

Line 4: ID returns the ISO day of the week, 1 = Monday.

Line 5: IW returns the ISO week of the year.

Line 6: Q returns the quarter number.

The results are:

Day DAY DoW ISO DoW ISO WoY Qtr --- --- --- ------- ------- --- Wed WED 4 3 19 2

I think all of this is fantastic information that can be returned from the timestamp, letting it be so much more than just... a timestamp.

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.