• About Dangerous DBA
  • Table of Contents
Dangerous DBA A blog for those DBA's who live on the edge

Time to “English” – Friday Function

July 20, 2012 12:00 pm / 2 Comments / dangerousDBA

I though about starting a new series that I will add to over time the Friday function, I might not be able to create something as useful as Serge Rielau (@srielau) but maybe it will helpful to someone. Over the last few days I have been asked to some interesting things with “my” data like converting times to a more “friendly” format.

The ask

To convert my nicely cleansed times (19:47:45 or 04:23:32) from various non standard sources (the wonders of Big Data) into a time format that is more compatible with your average email reader (7:47pm or 4:23am) as opposed to a techie that might be all right with the nice 24 hour times. I did a lot of searching around and I could not find any inbuilt functions of DB2 LUW that would do this and so I had to create my own user defined function. Please if you know of anything feel free to correct me, but searching the info center then there was nothing.

DB2 Time to “English” – Code

CREATE FUNCTION GLOBAL.CONVERT_TIME_12H(IN_TIME TIME)
DETERMINISTIC
NO EXTERNAL ACTION
RETURNS VARCHAR(10)
LANGUAGE SQL
BEGIN ATOMIC
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Takes 24hrs time in gives back 12hrs time suffixed with AM or PM
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    DECLARE OUT_TIME VARCHAR(10);

    IF(INT(LEFT(IN_TIME,2)) <= 12) THEN
        SET OUT_TIME = LEFT(IN_TIME,5) || 'am';

        IF(INT(LEFT(IN_TIME,2)) IN (10,11)) THEN
            RETURN REPLACE(OUT_TIME,'.',':');
        ELSE
            RETURN REPLACE(SUBSTR(OUT_TIME,1),'.',':');
        END IF;

    ELSE
        IF(INT(LEFT(IN_TIME,2)) = 24) THEN
            SET OUT_TIME = '00:00am';
        ELSE
            SET OUT_TIME = LEFT((IN_TIME - 12 HOURS),5) || 'pm';
        END IF;

        RETURN REPLACE(OUT_TIME,'.',':');
    END IF;

END

As you can see it takes in standard DB2 time, and I think managed to get all the gotchas like midnight not being 12 hours less. The downside of this function is that the format becomes text and therefore not useable so would not recommend using it for anything but for human format. As I said before if you know of a function to do this in DB2 that is built in I would love to know.

Future articles

In future Friday Functions then I will look at covering non standard asks that I have been required to do. I think after this one I will be needing to create a function to “elongate” a date!



Posted in: Convert, DB2 Data Types, DB2 Development, IBM DB2 LUW, Time, UDF, User Defined Function, Varchar / Tagged: convert, DB2, IBM DB2 LUW, Time, UDF, User Defined Functions, Varchar

2 Thoughts on “Time to “English” – Friday Function”

  1. Norman Wong on July 26, 2012 at 5:41 pm said:

    To format timestamps into various print formats, you can use varchar_format. Here’s an example where I reformat the current timestamp into an “English” format:
    db2 “values(varchar_format(CHAR(current timestamp),’DAY MONTH DD, YYYY HH12:MM:SS AM’))”
    ================================
    THURSDAY JULY 26, 2012 12:07:12 PM

    To take non-standard inputs and turn them into timestamps for DB2, the TIMESTAMP_FORMAT can be used. Arguments are the input string and a mask to describe it to db2.

    Reply↓
    • dangerousDBA on July 26, 2012 at 8:50 pm said:

      Thank you for that, how would you get the th, st, nd and rd onto it?

      Reply↓

Leave a Reply Cancel reply

Post Navigation

← Previous Post
Next Post →

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 757 other subscribers

Recent Posts

  • Self generating Simple SQL procedures – MySQL
  • Google Cloud Management – My Idea – My White Whale?
  • Position Tracker – The Stub – Pandas:
  • Position Tracker – The Stub
  • Position Tracker – In the beginning
  • Whats been going on in the world of the Dangerous DBA:
  • QCon London Day 1
  • Testing Amazon Redshift: Distribution keys and styles
  • Back to dangerous blogging
  • DB2 10.1 LUW Certification 611 notes 1 : Physical Design

Dangerous Topics

added functionality ADMIN_EST_INLINE_LENGTH Amazon Bootcamp colum convert data types db2 DB2 DB2 Administration DB2 Development db2advis db2licm Decompose XML Exam EXPORT Google IBM IBM DB2 LUW idug information centre infosphere LOAD merry christmas and a happy new year Recursive Query Recursive SQL Redshift Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sqlcode sql statement Stored Procedures SYSPROC.ADMIN_CMD Time UDF User Defined Functions V9.7 V10.1 Varchar XML XML PATH XMLTABLE

DangerousDBA Links

  • DB2 for WebSphere Commerce
  • My Personal Blog

Disclaimer:

The posts here represent my personal views and not those of my employer. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. DB2 is a trademark of IBM. I am not an employee or representative of IBM.

Advertising

© Copyright 2021 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress