<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"\@MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:326517032;
mso-list-type:hybrid;
mso-list-template-ids:-280482174 134807567 134807577 134807579 134807567 134807577 134807579 134807567 134807577 134807579;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><![if !supportLists]><span style="color:#1F497D"><span style="mso-list:Ignore">1.<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span style="color:#1F497D">Done. Big Tick. There were tears.<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><![if !supportLists]><span style="color:#1F497D"><span style="mso-list:Ignore">2.<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span style="color:#1F497D">Played a bit more with the format and we’ve worked out what we were doing wrong that caused the “ ON “ to be required.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Marty<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Laframboise, André (BAC/LAC) [mailto:andre.laframboise@canada.ca]
<br>
<b>Sent:</b> 10 July 2017 12:16<br>
<b>To:</b> Martin Bowes; info-ingres@lists.planetingres.org<br>
<b>Subject:</b> RE: [Info-ingres] reformatting strings as dates<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><a name="_MailEndCompose"><span lang="EN-CA" style="color:#1F497D;mso-fareast-language:EN-US">Sure solution #2 may catch most of them but I would still implement solution #1 anyways …..</span></a><span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA" style="color:#1F497D;mso-fareast-language:EN-US"> </span><span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="FR" style="color:black">André Laframboise<br>
<br>
Conseiller Principal Base de Données, Direction générale de l'Innovation et du Dirigeant principal de l'information<br>
</span><a href="http://www.bac-lac.gc.ca/fra/Pages/bac-web.aspx"><span lang="FR">Bibliothèque et Archives Canada</span></a><span lang="FR" style="color:black"> / Gouvernement du Canada
<br>
</span><a href="mailto:andre.laframboise@canada.ca"><span lang="FR">andre.laframboise@canada.ca</span></a><span lang="FR" style="color:black"> / Tél. : 613-298-1346<br>
<br>
Senior Database Advisor, Innovation and Chief Information Officer Branch<br>
</span><a href="http://www.bac-lac.gc.ca/eng/Pages/lac-web.aspx"><span lang="FR">Library and Archives Canada</span></a><span lang="FR" style="color:black"> / Government of Canada<br>
</span><a href="mailto:andre.laframboise@canada.ca"><span lang="FR">andre.laframboise@canada.ca</span></a><span lang="FR" style="color:black"> / Tel: 613-298-1346<br>
<br>
</span><a href="http://www.bac-lac.gc.ca/eng/events/Pages/events.aspx"><span lang="EN-CA" style="border:solid windowtext 1.0pt;padding:0cm;text-decoration:none"><img border="0" width="320" height="112" id="_x0000_i1025" src="cid:image001.jpg@01D2F977.C9757BF0" alt="Image removed by sender. http://www.bac-lac.gc.ca/eng/events/PublishingImages/banner.jpg"></span></a><span lang="FR" style="color:black"><br>
</span><span lang="EN-CA"><o:p></o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US">From:</span></b><span lang="EN-US"> info-ingres-bounces@lists.planetingres.org [mailto:info-ingres-bounces@lists.planetingres.org]
<b>On Behalf Of </b>Martin Bowes<br>
<b>Sent:</b> Monday, July 10, 2017 5:52 AM<br>
<b>To:</b> info-ingres@lists.planetingres.org<br>
<b>Subject:</b> [Info-ingres] reformatting strings as dates</span><span lang="EN-CA"><o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-CA"> <o:p></o:p></span></p>
<p class="MsoNormal">Hi All,<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"> <span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal">I just have to share this one….<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"> <span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal">User request:<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt">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.
<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt">An example of a stored value is: s=‘9:35 am on Fri, 07/07/2017’.
<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"> <span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal">The solution:<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-18.0pt">1.<span style="font-size:7.0pt;font-family:"Times New Roman","serif"">
</span>Yell at programmer for ever storing dates like this to begin with.<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-18.0pt">2.<span style="font-size:7.0pt;font-family:"Times New Roman","serif"">
</span> Geraint reminded me of the to_timestamp function in 10.2<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:18.0pt"> <span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"> <span style="font-family:"Courier New"">cast(</span><span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:18.0pt"><span style="font-family:"Courier New"">to_timestamp(uppercase(your_date), 'HH12:MI AM" ON "DY, DD/MM/YYYY')</span><span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> as ingresdate</span><span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> )</span><span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> </span><span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal">Neat!<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"> <span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal">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.<span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal"> <span lang="EN-CA"><o:p></o:p></span></p>
<p class="MsoNormal">Martin Bowes<span lang="EN-CA"><o:p></o:p></span></p>
</div>
</body>
</html>