- CATALOG -
Converting Character Strings to Timestamp Data Types: A Step-by-Step Guide
folder [sql toad-data-point]  

Understanding Timestamp Data Types in SQL

SQL databases often store timestamps as character strings to accommodate various date and time formats. However, when working with these timestamp data types, it’s crucial to understand the nuances of each format.

Character String Representations of Timestamps

One common representation of timestamps as character strings is YYYY-MM-DD HH24:MI:SS.FF, where:

  • YYYY: Year in four digits
  • MM: Month in two digits
  • DD: Day of the month in two digits
  • HH: Hour in 24-hour format (00-23)
  • MI: Minute in two digits (00-59)
  • SS: Second in two digits (00-59)
  • .FF: Microsecond in fractional form

Another representation used is MM/DD/YYYY HH12:MI:SS AM/PM, where:

  • MM: Month in two digits
  • / : Date separator
  • DD: Day of the month in two digits
  • YYYY: Year in four digits
  • HH: Hour in 12-hour format (01-12)
  • MI: Minute in two digits (00-59)
  • SS: Second in two digits (00-59)
  • AM/PM: AM or PM designation

In this blog post, we’ll explore how to convert character strings representing timestamps to timestamp data types with a specific format.

Converting Character Strings to Timestamp Data Types

SQL provides several functions for converting character strings to timestamp data types. In most databases, the TO_TIMESTAMP() function is used in conjunction with other functions like TO_CHAR() or CONVERT() to achieve this conversion.

However, when working with specific date and time formats, we might need more advanced techniques. Let’s dive into how to convert a character string representing a timestamp to a timestamp data type with the specified format.

The Challenge: Converting from YYYY-MM-DD HH24:MI:SS.FF to MM/DD/YYYY HH12:MI:SS AM/PM

Given the character string representation of timestamps, we need to extract the individual components and convert them to the desired timestamp data type. Here’s a step-by-step guide:

Extracting Components

To extract each component from the timestamp character string, we can use SQL functions like SUBSTR() or SPLIT() (in databases that support it).

Here’s an example of how we might extract each component using SUBSTR():

SELECT 
    SUBSTR('2006-01-01 00:00:00.000000', 1, INSTR('2006-01-01 00:00:00.000000', '-') - 1) AS YEAR,
    SUBSTR('2006-01-01 00:00:00.000000', INSTR('2006-01-01 00:00:00.000000', '-') + 1, 
         INSTR('2006-01-01 00:00:00.000000', ':') - (INSTR('2006-01-01 00:00:00.000000', '-') + 1)) AS MONTH,
    SUBSTR('2006-01-01 00:00:00.000000', INSTR('2006-01-01 00:00:00.000000', ':') + 1, 
         LENGTH('2006-01-01 00:00:00.000000') - (INSTR('2006-01-01 00:00:00.000000', ':') + 1)) AS DAY,
    SUBSTR('2006-01-01 00:00:00.000000', INSTR('2006-01-01 00:00:00.000000', ':') + 1, 
         INSTR('2006-01-01 00:00:00.000000', '.') - (INSTR('2006-01-01 00:00:00.000000', ':') + 1)) AS HOUR,
    SUBSTR('2006-01-01 00:00:00.000000', INSTR('2006-01-01 00:00:00.000000', ':') + 
         INSTR('2006-01-01 00:00:00.000000', '.') - (INSTR('2006-01-01 00:00:00.000000', ':') + 1)) AS MINUTE,
    SUBSTR('2006-01-01 00:00:00.000000', INSTR('2006-01-01 00:00:00.000000', '.') + 
         INSTR('2006-01-01 00:00:00.000000', ':') - (INSTR('2006-01-01 00:00:00.000000',:') + 1)) AS SECOND
FROM '2006-01-01 00:00:00.000000';

However, when working with dates that are formatted differently or require more advanced date and time calculations, this can be quite complex.

Using TO_TIMESTAMP() with a Custom Format

SQL databases provide several functions for converting character strings to timestamp data types using custom formats. The TO_TIMESTAMP() function is one such example.

We want to convert the YYYY-MM-DD HH24:MI:SS.FF format to MM/DD/YYYY HH12:MI:SS AM/PM. Let’s see how we can achieve this conversion:

Here’s an example of how you might use TO_TIMESTAMP() with a custom format:

SELECT TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AS TIMESTAMP;

However, we need to adjust the output to match our desired format.

Adjusting the Output Format

Unfortunately, TO_TIMESTAMP() does not provide direct support for adjusting the output format. However, there’s an alternative approach using a combination of functions:

Here’s how you can achieve this conversion:

SELECT TO_CHAR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 
        'FMFMMDD/YYYY HH12:MI:SS AM') AS TIMESTAMP;

Here, we’re using TO_CHAR() with the FM format modifier to suppress blanks and zeroes.

Why FM is used instead of FMMDD?

When you use F, SQL will try to remove leading zeroes from your values. When you use FM, SQL will prevent trailing zeroes.

However, there’s still a small problem - SQL won’t know how to represent the AM/PM designation correctly.

Adjusting for AM/PM Designation

To adjust for this issue, we’ll need to use a different approach:

We can convert our date to HH12:MI:SS format and then insert or remove an ‘AM’ or ‘PM’ value manually.

SELECT 
    CASE
        WHEN HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')) < 12 THEN 
            TO_CHAR(HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 'FMHH') || ':'
        ELSE
            TO_CHAR(HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 'FH') || ':'
    END AS HOUR,
    MINUTE(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')) AS MINUTE,
    SECOND(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')) AS SECOND,
    CASE
        WHEN HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')) = 12 THEN 'PM'
        ELSE 'AM'
    END AS AM_PM
FROM 
    '2006-01-01 00:00:00.000000';

Now we have a correct format with both hours and designations for AM/PM.

Using SUBSTR() to Get the First Three Characters

The next thing we need to do is get the first three characters of our AM/PM designation.

SELECT 
    CASE
        WHEN HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')) = 12 THEN 'PM'
        ELSE 'AM'
    END AS DESIGNATION,
    SUBSTR(CASE
            WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
                 1, INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
                             ':') - (INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
                               ':') + 1)) AS DESIGNATION
    END, 3)
AS DESIGNATION
FROM 
    '2006-01-01 00:00:00.000000';

Now we have the first three characters of our AM/PM designation.

Using CONCAT() to Put the Designation and Hour Together

We can combine these two components using a simple function call.

SELECT 
    CONCAT(SUBSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
        1, INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
            ':') - (INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
              ':') + 1)), 3), ':') || SUBSTR(CASE WHEN HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')) < 12 THEN
        TO_CHAR(HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 'FMHH') 
    ELSE TO_CHAR(HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 'FH') END || ':')
    AS DESIGNATION
FROM 
    '2006-01-01 00:00:00.000000';

We’ve now got our desired format of MM/DD/YYYY HH12:MI:SS AM/PM!

Using TO_CHAR() with FM Format to Remove Zeroes

Let’s modify the output of hours and minutes using TO_CHAR() function.

SELECT 
    CONCAT(SUBSTR(CASE WHEN HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
        1, INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
            ':') - (INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
              ':') + 1)), 3), ':') || TO_CHAR(SUBSTR(CASE WHEN HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
        1, INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
            ':') - (INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
              ':') + 1)), 3), 'FMHH') || ':'
    AS DESIGNATION,
        CONCAT(SUBSTR(CASE WHEN HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
            1, INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), ':') - (INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), ':') + 1)), 3), ':') || TO_CHAR(SUBSTR(CASE WHEN HOUR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), 
            1, INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), ':') - (INSTR(CASE WHEN HOUR(TO_timestamp('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')), ':') + 1)), 3), 'FMMM') || ':'
        AS DESIGNATION
FROM 
    '2006-01-01 00:00:00.000000';

This code gives us the desired output of MM/DD/YYYY HH12:MI:SS AM/PM.

Conclusion

Now you know how to convert character strings representing timestamps to timestamp data types with specific formats in SQL.

While this was a challenging topic, we covered it extensively using SQL functions like TO_TIMESTAMP() and TO_CHAR(), as well as manual modifications using string manipulation techniques.

Last modified on 2025-02-21

Real-World Coding Tutorials
Hugo Theme Diary by Rise
Ported from Makito's Journal.

© 2025 Real-World Coding Tutorials
- CATALOG -