Hi All,
In my innocence I assumed that the 'is ingresdate' predicate would work using the current session setting of II_DATE_FORMAT. That variable can after all be set locally to override the global setting used in the installation. Given that many input date
formats are valid in some settings but not in others, using the session setting makes sense to me.
So for example my installation has II_DATE_FORMAT=multinational4. So todays date would be generated as '06/04/2021'
But...
EXPORT II_DATE_FORMAT=SWEDEN
sql iidbdb <<SQL_END
select dbmsinfo('date_format');
col1
finland
select date('today')\g
col1
2021-04-06
(1 row)
select case when '2021-04-06' is ingresdate then 'valid' else 'invalid' end\g
col1
invalid <-- Well that's a bit of a shock.
(1 row)
select case when '06/04/2021' is ingresdate then 'valid' else 'invalid' end
col1
valid <-- This is correct, as that date string is valid date input in Sweden
(1 row)
So is the 'is ingresdate' predicate only respecting the installation wide setting of II_DATE_FORMAT or is this a bug in the processing?
Note I've tried the above using the set date_format 'sweden' command with the same result.
Martin Bowes
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=koi8-r">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)"> <style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@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;}
@font-face
{font-family:"Lucida Console";
panose-1:2 11 6 9 4 5 4 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
</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="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">In my innocence I assumed that the ‘is ingresdate’ predicate would work
<u>using the current session setting</u> of II_DATE_FORMAT. That variable can after all be set locally to override the global setting used in the installation. Given that many input date formats are valid in some settings but not in others, using the
session
setting makes sense to me.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">So for example my installation has II_DATE_FORMAT=multinational4. So todays date would be generated as ‘06/04/2021’<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">But…<o:p></o:p></p>
<p class="MsoNormal">EXPORT II_DATE_FORMAT=SWEDEN<o:p></o:p></p>
<p class="MsoNormal">sql iidbdb <<SQL_END<o:p></o:p></p>
<p class="MsoNormal">select dbmsinfo(‘date_format’);<o:p></o:p></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">col1 &
nbsp; <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">finland &
nbsp; <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">select date('today')\g<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">col1 <o:p></o:
</span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">2021-04-06 <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">(1 row)<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">select case when '2021-04-06' is ingresdate then 'valid' else 'invalid' end\g<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">col1 <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">invalid
</span><span style="font-size:10.0pt;font-family:Wingdings">ß</span><span style="font-size:10.0pt;font-family:"Lucida Console""> Well that’s a bit of a shock.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">(1 row)<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">select case when '06/04/2021' is ingresdate then 'valid' else 'invalid' end<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">col1 <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">valid </span><span style="font-size:10.0pt;font-family:Wingdings">ß</span><span style="font-size:10.0pt;font-family:"
Lucida Console""> This is correct,
as that date string is valid date input in Sweden<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">(1 row)<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">So is the ‘is ingresdate’ predicate only respecting the installation wide setting of II_DATE_FORMAT or is this a bug in the processing?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Note I’ve tried the above using the set date_format ‘sweden’ command with the same result.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Martin Bowes<o:p></o:p></p>
</div>
</body>
</html>
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)