Date calculations

Thu, 3 Feb 2005 07:41:05 -0000

This is a multi-part message in MIME format.

------_=_NextPart_001_01C509C3.B78B5746
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Derek,
=20
I agree that the syntax you have used is more readable and it's part of
the date function that I had not realised was there.  The system I'm
working on has been around since about 1985 and inherent through the
code is the 'date(days(sysdate)-1)' type of calculation which more than
anything is less efficient than 'date(-1, sysdate)'.  Is this feature a
recent edition? (remember I'm talking the last 20 years!!!!).
=20
Thanks for teaching a 'not too old a dog' a new trick.  (I did spot your
previous message about this feature).
=20
Regards,
Gavin.=20

	-----Original Message-----
	From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com] On Behalf Of Fyfield, Derek
	Sent: 03 February 2005 06:05
	To: Kristina Carlton; 'powerh-l@lists.sowder.com'
	Subject: RE: Date calculations
=09
=09

	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.

	=20

	DEFINE RUNDT DATE    =3D DATE(-1, sysdate)

	=20

	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).

	=20

	Regards,

	=20

	Derek.

	=20

	=20

=09
  _____ =20

	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

	=20

	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:

	=20

	DEFINE RUNDT DATE    =3D DATE(DAYS(SYSDATE - 1))

	=20

	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.

	=20

	We have OpenVMS V7.3-2 and I am using a Quiz extract.

	=20

	Thanks,

	Kristina Carlton

	Senior Programmer/Analyst

	The JI Companies

	Kristina.Carlton@JICompanies.com

	=20

	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_01C509C3.B78B5746
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns=3D"http://www.w3.org/TR/REC-html40" xmlns:v =3D=20
"urn:schemas-microsoft-com:vml" xmlns:o =3D=20
"urn:schemas-microsoft-com:office:office" xmlns:w =3D=20
"urn:schemas-microsoft-com:office:word"><HEAD><TITLE>Message</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2800.1479" name=3DGENERATOR><!--[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 vLink=3Dpurple link=3Dblue>
<DIV><SPAN class=3D880293507-03022005><FONT face=3DTahoma =
color=3D#0000ff=20
size=3D2>Derek,</FONT></SPAN></DIV>
<DIV><SPAN class=3D880293507-03022005><FONT face=3DTahoma =
color=3D#0000ff=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D880293507-03022005><FONT face=3DTahoma =
color=3D#0000ff size=3D2>I=20
agree that the syntax you have used is more readable and it's part of =
the date=20
function that I had not realised was there.&nbsp; The system I'm working =
on has=20
been around since about 1985 and inherent through the code is the=20
'date(days(sysdate)-1)' type of calculation which more than anything is =
less=20
efficient than 'date(-1, sysdate)'.&nbsp; Is this feature a recent =
edition?=20
(remember I'm talking the last 20 years!!!!).</FONT></SPAN></DIV>
<DIV><SPAN class=3D880293507-03022005><FONT face=3DTahoma =
color=3D#0000ff=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D880293507-03022005><FONT face=3DTahoma =
color=3D#0000ff=20
size=3D2>Thanks for teaching a 'not too old a dog' a new trick.&nbsp; (I =
did spot=20
your previous message about this feature).</FONT></SPAN></DIV>
<DIV><SPAN lang=3Den-gb><FONT face=3DTahoma color=3D#0000ff=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN lang=3Den-gb><FONT face=3DTahoma><FONT color=3D#0000ff><FONT =
size=3D2><SPAN=20
class=3D880293507-03022005>Regards,</SPAN></FONT></FONT></FONT></SPAN></D=
IV>
<DIV><SPAN lang=3Den-gb><FONT face=3DTahoma><FONT color=3D#0000ff><FONT =
size=3D2><SPAN=20
class=3D880293507-03022005>G</SPAN>avin.</FONT></FONT></FONT></SPAN> =
</DIV>
<BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
  <DIV></DIV>
  <DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr =
align=3Dleft><FONT=20
  face=3DTahoma size=3D2>-----Original Message-----<BR><B>From:</B>=20
  powerh-l-admin@lists.sowder.com =
[mailto:powerh-l-admin@lists.sowder.com] <B>On=20
  Behalf Of </B>Fyfield, Derek<BR><B>Sent:</B> 03 February 2005=20
  06:05<BR><B>To:</B> Kristina Carlton;=20
  'powerh-l@lists.sowder.com'<BR><B>Subject:</B> RE: Date=20
  calculations<BR><BR></FONT></DIV>
  <DIV class=3DSection1>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">The centre =
ot the=20
  expression in your example is &#8220;sysdate &#8211; 1&#8221;, and =
this syntax is not always=20
  going to yield a true date. I would use the following as it makes the=20
  expression simpler to read and reliable =
too.<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
Arial"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">DEFINE =
RUNDT=20
  DATE&nbsp;&nbsp;&nbsp; =3D DATE(-1, =
sysdate)<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
Arial"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">The DATE =
function=20
  takes two arguments.&nbsp; The first is a numeric expression (here it =
is -1)=20
  and the second is a base date (and here it is=20
  sysdate).<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
Arial"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
Arial">Regards,<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
Arial"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
Arial">Derek.<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
Arial"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DArial color=3Dnavy size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
Arial"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <DIV>
  <DIV class=3DMsoNormal style=3D"TEXT-ALIGN: center" =
align=3Dcenter><FONT=20
  face=3D"Times New Roman" color=3Dblack size=3D3><SPAN=20
  style=3D"FONT-SIZE: 12pt; COLOR: windowtext; FONT-FAMILY: 'Times New =
Roman'">
  <HR tabIndex=3D-1 align=3Dcenter width=3D"100%" SIZE=3D2>
  </SPAN></FONT></DIV>
  <P class=3DMsoNormal><B><FONT face=3DTahoma color=3Dblack =
size=3D2><SPAN=20
  style=3D"FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: windowtext; =
FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT=20
  face=3DTahoma color=3Dblack size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: windowtext; FONT-FAMILY: Tahoma"> =
Kristina=20
  Carlton [mailto:Kristina.Carlton@JICompanies.com] <BR><B><SPAN=20
  style=3D"FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday, February 02, =
2005 7:12=20
  PM<BR><B><SPAN style=3D"FONT-WEIGHT: bold">To:</SPAN></B>=20
  'powerh-l@lists.sowder.com'<BR><B><SPAN=20
  style=3D"FONT-WEIGHT: bold">Subject:</SPAN></B> Date=20
  calculations</SPAN></FONT><FONT face=3D"Times New Roman" color=3Dblack =

size=3D3><SPAN=20
 style=3D"FONT-SIZE: 12pt; COLOR: windowtext; FONT-FAMILY: 'Times New =
Roman'"><o:p></o:p></DIV>
 <o:p>&nbsp;</o:p>
 Yesterday some =
emails were=20
 exchanged on calculating dates and that &#8220;DATE &#8211; 1&#8221; =
came up with an incorrect=20
 date of 02.00.2005. I calculate my date with the following=20
 syntax:<o:p></o:p>
 <SPAN=20
 style=3D"FONT-SIZE: 7.5pt"><o:p>&nbsp;</o:p>
 DEFINE RUNDT=20
 DATE&nbsp;&nbsp;&nbsp; =3D DATE(DAYS(SYSDATE -=20
 1))<o:p></o:p>
 <SPAN=20
 style=3D"FONT-SIZE: 7.5pt"><o:p>&nbsp;</o:p>
 This has worked =
every night=20
 except yesterday &#8211; February 1st. When I display RUNDT =
it actually=20
 has Januray 31st, 2005 but when I run the extract I get =
zero=20
 records. If I hard code January 31st, 2005 I do get records =
back.=20
 Am I doing something wrong? I got my data, but I would like to =
understand why=20
 this is happening.<o:p></o:p>
 <SPAN=20
 style=3D"FONT-SIZE: 7.5pt"><o:p>&nbsp;</o:p>
 We have OpenVMS =
V7.3-2 and I am=20
 using a Quiz extract.<o:p></o:p>
 <SPAN=20
 style=3D"FONT-SIZE: 7.5pt"><o:p>&nbsp;</o:p>
 <SPAN=20
 style=3D"FONT-SIZE: 9pt">Thanks,<o:p></o:p>
 Kristina Carlton<o:p></o:p>
 Senior=20
 Programmer/Analyst<o:p></o:p>
 The JI=20
 Companies<o:p></o:p>
 <SPAN=20
 style=3D"FONT-SIZE: =
9pt">Kristina.Carlton@JICompanies.com<o:p></o:p>=

<o:p>&nbsp;</o:p></DIV> 
 This message and any attachments =
(the 'message')=20
 is intended solely for the addressees and is confidential. If you =
receive this=20
 message in error, please delete it and immediately notify the sender. =
Any use=20
 not in accord with its purpose, any dissemination or disclosure, =
either whole=20
 or partial, is prohibited, except with formal approval. The Internet =
cannot=20
 guarantee the integrity of this message. Orbit (and its subsidiaries) =
shall=20
 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=20
  email.</FONT></I></P><BR><BR></BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C509C3.B78B5746--