Converting ISO Dates to Text Representations in SQL

This is more for my own benefit than for anyone else, but I can see this question cropping up a few more times over the next few months. The issue is that I have a table containing an ISO date and have to populate a legacy table in which the date is represented as a ten character string (YYYY-MM-DD).

This works:

select trim(char(year(iso_date))) || '-' ||
       substr(digits(month(iso_date)), 9, 2) || '-' ||
       substr(digits(char(day(iso_date))), 31, 2)
from ...

The digits function returns a fixed-length character-string representation of the absolute value of a number. then all you need to do is identify the bit of the string that you need to use.

5 thoughts on “Converting ISO Dates to Text Representations in SQL

  1. If it’s the || symbols that caused that caused the question, these are concatenation tokens. They’re not standard SQL, but very handy when you have more than two strings to concatenate.


  2. Ah. Fair Enough. There’s T-SQL functions that make this easier. I work with a DB whizz and he’s got all kinds of good stuff for dealing with the knottiest string parsing/date conversion challenges.


  3. Thanks for the thought. DB2 is pretty good at date handling – the problem is that whoever wrote the (soon th be gone) legacy application managed to avoid using any of this functionality at all.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s