[Info-ingres] reformatting strings as dates

[Martin Bowes]

Hi All,

I just have to share this one....

User request:
There are some date/time values in one of the tables that have been stored as string type and I should convert them to DATE again.
An example of a stored value is: s='9:35 am on Fri, 07/07/2017'.

The solution:

1.       Yell at programmer for ever storing dates like this to begin with.

2.        Geraint reminded me of the to_timestamp function in 10.2

    cast(
to_timestamp(uppercase(your_date), 'HH12:MI AM" ON "DY, DD/MM/YYYY')
    as ingresdate
    )

Neat!

It's a little flaky on some of the parameters and we have found the best option is just to convert to uppercase. One possible source of disaster is that at the  moment it appears the DD and MMs have to be two characters, so 1/3 is not permitted but 01/03 is. We've also found that the format allowing for the phrase 'on' must have surrounding spaces so that "ON" won't work but " ON " will.

Martin Bowes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20170710/3ed4f44a/attachment.html>