[Info-ingres] reformatting strings as dates

[Martin Bowes]

1.       Done. Big Tick. There were tears.

2.       Played a bit more with the format and we've worked out what we were doing wrong that caused the " ON " to be required.

Marty

From: Laframboise, André (BAC/LAC) [mailto:andre.laframboise at canada.ca]
Sent: 10 July 2017 12:16
To: Martin Bowes; info-ingres at lists.planetingres.org
Subject: RE: [Info-ingres] reformatting strings as dates

Sure solution #2 may catch most of them but I would still implement solution #1 anyways .....

André Laframboise

Conseiller Principal Base de Données, Direction générale de l'Innovation et du Dirigeant principal de l'information
Bibliothèque et Archives Canada<http://www.bac-lac.gc.ca/fra/Pages/bac-web.aspx> / Gouvernement du Canada
andre.laframboise at canada.ca<mailto:andre.laframboise at canada.ca> / Tél. : 613-298-1346

Senior Database Advisor, Innovation and Chief Information Officer Branch
Library and Archives Canada<http://www.bac-lac.gc.ca/eng/Pages/lac-web.aspx> / Government of Canada
andre.laframboise at canada.ca<mailto:andre.laframboise at canada.ca> / Tel: 613-298-1346

[Image removed by sender. http://www.bac-lac.gc.ca/eng/events/PublishingImages/banner.jpg]<http://www.bac-lac.gc.ca/eng/events/Pages/events.aspx>

From: info-ingres-bounces at lists.planetingres.org [mailto:info-ingres-bounces at lists.planetingres.org] On Behalf Of Martin Bowes
Sent: Monday, July 10, 2017 5:52 AM
To: info-ingres at lists.planetingres.org
Subject: [Info-ingres] reformatting strings as dates

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/78c11be1/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 752 bytes
Desc: image001.jpg
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20170710/78c11be1/attachment.jpg>