Date calculations
Fyfield, Derek
DEREK.FYFIELD@orbit.net
Thu, 3 Feb 2005 09:05:23 +0300
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C509B6.5968D91F
Content-Type: text/plain
The centre ot the expression in your example is "sysdate - 1", and this
syntax is not always going to yield a true date. I would use the following
as it makes the expression simpler to read and reliable too.
DEFINE RUNDT DATE = DATE(-1, sysdate)
The DATE function takes two arguments. The first is a numeric expression
(here it is -1) and the second is a base date (and here it is sysdate).
Regards,
Derek.
_____
From: Kristina Carlton [mailto:Kristina.Carlton@JICompanies.com]
Sent: Wednesday, February 02, 2005 7:12 PM
To: 'powerh-l@lists.sowder.com'
Subject: Date calculations
Yesterday some emails were exchanged on calculating dates and that "DATE -
1" came up with an incorrect date of 02.00.2005. I calculate my date with
the following syntax:
DEFINE RUNDT DATE = DATE(DAYS(SYSDATE - 1))
This has worked every night except yesterday - February 1st. When I display
RUNDT it actually has Januray 31st, 2005 but when I run the extract I get
zero records. If I hard code January 31st, 2005 I do get records back. Am I
doing something wrong? I got my data, but I would like to understand why
this is happening.
We have OpenVMS V7.3-2 and I am using a Quiz extract.
Thanks,
Kristina Carlton
Senior Programmer/Analyst
The JI Companies
Kristina.Carlton@JICompanies.com
This message and any attachments (the 'message') is intended solely for the
addressees and is confidential. If you receive this message in error, please
delete it and immediately notify the sender. Any use not in accord with its
purpose, any dissemination or disclosure, either whole or partial, is
prohibited, except with formal approval. The Internet cannot guarantee the
integrity of this message. Orbit (and its subsidiaries) shall therefore not
be liable for the message, if modified. Orbit accepts no liability for any
damage caused by any error or virus transmitted by this email.
------_=_NextPart_001_01C509B6.5968D91F
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (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:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Century Gothic";
panose-1:2 11 5 2 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:9.0pt;
font-family:"Century Gothic";
color:black;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p.MsoAutoSig, li.MsoAutoSig, div.MsoAutoSig
{margin:0in;
margin-bottom:.0001pt;
font-size:9.0pt;
font-family:"Century Gothic";
color:black;}
span.EmailStyle16
{mso-style-type:personal;
font-family:"Century Gothic";
color:navy;
font-weight:normal;
font-style:normal;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=3DEN-US link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>The centre ot the expression in =
your
example is “sysdate – 1”, and this syntax is not =
always going
to yield a true date. I would use the following as it makes the =
expression simpler
to read and reliable too.<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>=
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>DEFINE RUNDT =
DATE =3D
DATE(-1, sysdate)<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>=
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>The DATE function takes two
arguments. The first is a numeric expression (here it is -1) and =
the
second is a base date (and here it is =
sysdate).<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>=
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Regards,<o:p></o:p></span></font></=
p>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>=
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Derek.<o:p></o:p></span></font></p>=
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>=
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>=
<div>
<div class=3DMsoNormal align=3Dcenter style=3D'text-align:center'><font =
size=3D3
color=3Dblack face=3D"Times New Roman"><span =
style=3D'font-size:12.0pt;font-family:
"Times New Roman";color:windowtext'>
<hr size=3D2 width=3D"100%" align=3Dcenter tabindex=3D-1>
</span></font></div>
<p class=3DMsoNormal><b><font size=3D2 color=3Dblack =
face=3DTahoma><span
style=3D'font-size:10.0pt;font-family:Tahoma;color:windowtext;font-weigh=
t:bold'>From:</span></font></b><font
size=3D2 color=3Dblack face=3DTahoma><span =
style=3D'font-size:10.0pt;font-family:Tahoma;
color:windowtext'> Kristina Carlton =
[mailto:Kristina.Carlton@JICompanies.com] <br>
<b><span style=3D'font-weight:bold'>Sent:</span></b> Wednesday, =
February 02, 2005
7:12 PM<br>
<b><span style=3D'font-weight:bold'>To:</span></b> =
'powerh-l@lists.sowder.com'<br>
<b><span style=3D'font-weight:bold'>Subject:</span></b> Date =
calculations</span></font><font
size=3D3 color=3Dblack face=3D"Times New Roman"><span =
style=3D'font-size:12.0pt;
font-family:"Times New =
Roman";color:windowtext'><o:p></o:p></span></font></p>
</div>
<p class=3DMsoNormal><font size=3D1 color=3Dblack face=3D"Century =
Gothic"><o:p> </o:p></font></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span style=3D'font-size:9.0pt'>Yesterday some =
emails were
exchanged on calculating dates and that “DATE – 1” =
came up
with an incorrect date of 02.00.2005. I calculate my date with the =
following
syntax:<o:p></o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span =
style=3D'font-size:7.5pt'><o:p> </o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span style=3D'font-size:9.0pt'>DEFINE RUNDT
DATE =3D DATE(DAYS(SYSDATE - =
1))<o:p></o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span =
style=3D'font-size:7.5pt'><o:p> </o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span style=3D'font-size:9.0pt'>This has worked =
every night
except yesterday – February 1<sup>st</sup>. When I display RUNDT =
it
actually has Januray 31<sup>st</sup>, 2005 but when I run the extract I =
get
zero records. If I hard code January 31<sup>st</sup>, 2005 I do get =
records
back. Am I doing something wrong? I got my data, but I would like to =
understand
why this is happening.<o:p></o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span =
style=3D'font-size:7.5pt'><o:p> </o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span style=3D'font-size:9.0pt'>We have OpenVMS =
V7.3-2 and
I am using a Quiz extract.<o:p></o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span =
style=3D'font-size:7.5pt'><o:p> </o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span =
style=3D'font-size:9.0pt'>Thanks,<o:p></o:p></span></font></span></p>
<p class=3DMsoNormal><span
class=3DEmailStyle16><font color=3Dnavy face=3D"Century =
Gothic">Kristina Carlton<o:p></o:p></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span style=3D'font-size:9.0pt'>Senior =
Programmer/Analyst<o:p></o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span style=3D'font-size:9.0pt'>The JI =
Companies<o:p></o:p></span></font></span></p>
<p class=3DMsoNormal><span class=3DEmailStyle16><font size=3D1 =
color=3Dnavy
face=3D"Century Gothic"><span =
style=3D'font-size:9.0pt'>Kristina.Carlton@JICompanies.com<o:p></o:p></s=
pan></font></span></p>
<p class=3DMsoNormal><o:p> </o:p></p>
</div>
</body>
</html>
<BR>
<BR>
<P><I><FONT SIZE=3D2 FACE=3D"Arial">This message and any attachments =
(the 'message') is intended solely for the addressees and is =
confidential. If you receive this message in error, please delete it =
and immediately notify the sender. Any use not in accord with its =
purpose, any dissemination or disclosure, either whole or partial, is =
prohibited, except with formal approval. The Internet cannot guarantee =
the integrity of this message. Orbit (and its subsidiaries) shall =
therefore not be liable for the message, if modified. Orbit accepts no =
liability for any damage caused by any error or virus transmitted by =
this email.</FONT></I></P>
<BR>
<BR>
------_=_NextPart_001_01C509B6.5968D91F--