<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (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:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
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:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
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:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
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-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:455413431;
mso-list-type:hybrid;
mso-list-template-ids:-1297349188 134807567 134807577 134807579 134807567 134807577 134807579 134807567 134807577 134807579;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@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:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@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:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@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:0in;}
ul
{margin-bottom:0in;}
--></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-CA" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><a name="_MailEndCompose"><span style="color:#1F497D;mso-fareast-language:EN-US">Sure solution #2 may catch most of them but I would still implement solution #1 anyways …..<o:p></o:p></span></a></p>
<p class="MsoNormal"><span style="mso-bookmark:_MailEndCompose"><span style="color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_MailEndCompose"><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></span><span style="mso-bookmark:_MailEndCompose"></span><a href="http://www.bac-lac.gc.ca/fra/Pages/bac-web.aspx"><span style="mso-bookmark:_MailEndCompose"><span lang="FR">Bibliothèque et Archives Canada</span></span><span style="mso-bookmark:_MailEndCompose"></span></a><span style="mso-bookmark:_MailEndCompose"><span lang="FR" style="color:black">
/ Gouvernement du Canada <br>
</span></span><span style="mso-bookmark:_MailEndCompose"></span><a href="mailto:andre.laframboise@canada.ca"><span style="mso-bookmark:_MailEndCompose"><span lang="FR">andre.laframboise@canada.ca</span></span><span style="mso-bookmark:_MailEndCompose"></span></a><span style="mso-bookmark:_MailEndCompose"><span lang="FR" style="color:black">
/ Tél. : 613-298-1346<br>
<br>
Senior Database Advisor, Innovation and Chief Information Officer Branch<br>
</span></span><span style="mso-bookmark:_MailEndCompose"></span><a href="http://www.bac-lac.gc.ca/eng/Pages/lac-web.aspx"><span style="mso-bookmark:_MailEndCompose"><span lang="FR">Library and Archives Canada</span></span><span style="mso-bookmark:_MailEndCompose"></span></a><span style="mso-bookmark:_MailEndCompose"><span lang="FR" style="color:black">
/ Government of Canada<br>
</span></span><span style="mso-bookmark:_MailEndCompose"></span><a href="mailto:andre.laframboise@canada.ca"><span style="mso-bookmark:_MailEndCompose"><span lang="FR">andre.laframboise@canada.ca</span></span><span style="mso-bookmark:_MailEndCompose"></span></a><span style="mso-bookmark:_MailEndCompose"><span lang="FR" style="color:black">
/ Tel: 613-298-1346<br>
<br>
</span></span><a href="http://www.bac-lac.gc.ca/eng/events/Pages/events.aspx"><span style="mso-bookmark:_MailEndCompose"><span style="text-decoration:none"><img border="0" width="320" height="112" style="width:3.3333in;height:1.1666in" id="_x0000_i1025" src="http://www.bac-lac.gc.ca/eng/events/PublishingImages/banner.jpg" alt="http://www.bac-lac.gc.ca/eng/events/PublishingImages/banner.jpg"></span></span><span style="mso-bookmark:_MailEndCompose"></span></a><span style="mso-bookmark:_MailEndCompose"><span lang="FR" style="color:black"><br>
</span></span><span style="mso-bookmark:_MailEndCompose"><span lang="FR" style="color:#1F497D;mso-fareast-language:EN-US"><o:p></o:p></span></span></p>
<span style="mso-bookmark:_MailEndCompose"></span>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<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<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-GB">Hi All,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">I just have to share this one….<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">User request:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB">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.
<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB">An example of a stored value is: s=‘9:35 am on Fri, 07/07/2017’.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">The solution:<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo2"><![if !supportLists]><span lang="EN-GB"><span style="mso-list:Ignore">1.<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span lang="EN-GB">Yell at programmer for ever storing dates like this to begin with.<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo2"><![if !supportLists]><span lang="EN-GB"><span style="mso-list:Ignore">2.<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span lang="EN-GB"> Geraint reminded me of the to_timestamp function in 10.2<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.25in"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><span lang="EN-GB" style="font-family:"Courier New"">cast(<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.25in"><span lang="EN-GB" style="font-family:"Courier New"">to_timestamp(uppercase(your_date), 'HH12:MI AM" ON "DY, DD/MM/YYYY')<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-family:"Courier New""> as ingresdate<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-family:"Courier New""> )<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">Neat!<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">Martin Bowes<o:p></o:p></span></p>
</div>
</body>
</html>