b¬äd†ødd

Dedicated to design and performance of databases and audio systems.

Date Name Formats

From time to time I see SQL date logic that attempts to create an English version of dates. Most of these are more verbose than they need to be. So, in that spirit, below are some examples of how you can perform these far more simply and elegantly. After all, isn't that what coding is all about? :-)

The most common example is generating the day of the week's name (e.g. Friday). Typically, it is something to the effect of

select
    case syslib.day_of_week(current_date)
        when 1 then 'Sunday'
        when 2 then 'Monday'
        when 3 then 'Tuesday'
        when 4 then 'Wednesday'
        when 5 then 'Thursday'
        when 6 then 'Friday'
        when 7 then 'Saturday'
    end as day_name
;

That's nine lines of a simple CASE statement. Instead, you could perform the following

select 
    cast(
        cast(current_date as format 'E4') 
         as varchar(9))
    as day_name -- e.g. Friday
;

Or, if you would like the abbreviation, just execute

select 
    cast(
        cast(current_date as format 'E3') 
         as character(3)) 
    as day_abbreviation -- e.g. Fri
;

Far simpler than the CASE statement.

We can also do the same for the month name too. Instead of a CASE statement use either of the following

select 
    cast(
        cast(current_date as format 'M4') 
         as varchar(9))
    as month_name
; -- e.g. October

select 
    cast(
        cast(current_date as format 'M3') 
         as character(3)) 
    as month_abbreviation
; -- e.g. Oct

And, if you were looking for a text version of the month number then do

select 
    cast(
        cast(current_date as format 'MM') 
         as character(2)) 
    as month_number
; -- e.g. 10

Now, if you needed a text version of the day number there are a couple of options:

select 
    cast(
        cast(current_date as format 'DD') 
         as character(2))
    as day_of_month
; -- e.g. 04

select 
    cast(
        cast(current_date as format 'DDD') 
         as character(3)) 
    as day_of_year
; -- e.g. 277 (Julian Date)

Likewise, for a text version of the year

select 
    cast(
        cast(current_date as format 'Y4') 
         as character(4)) 
    as year_character
; -- e.g. 2014

Now FORMAT values of B are used for spaces and we can use the FORMAT to perform functions that we would otherwise concatenate together. As as example, the full English version of today's date is

select 
    cast(
        cast(current_date as format 'E4,BM4BDD,BY4') 
         as varchar(29)) 
    as the_date
; -- e.g. Thursday, January 9, 2014

Need YYYY-MM? Then

select 
    cast(
        cast(current_date as format 'Y4-MM') 
         as character(7)) 
    as year_month
; -- e.g. 2014-01

I hope this helps with some of your current and future English date formatting needs.

Cheers!
-Brad