beginning/end of month

Wed, 10 Nov 2004 12:55:54 -0500

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_01C4C74E.85D63100
Content-Type: text/plain

> define d-last date = date(days(round(sysdate,-2) + 1) - 1)
> define d-first date = round(d-last,-2) + 1

Of course it may need to be benchmarked with a substring extract approach
:-)

Regards
Dave

-----Original Message-----
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com] On Behalf Of Kristina Carlton
Sent: Wednesday, November 10, 2004 11:26 AM
To: 'powerh-l@lists.sowder.com'
Subject: beginning/end of month

Hi List!

I was wondering if someone can tell me the most efficient way to derived the
beginning and end of month. We are running an extract on the first of every
month and the date parameters need to be from the 1st through the last day
of the previous month. I know there is a MONTH function as well as LASTDAY,
DATEEXTRACT, etc. but deriving and converting the dates seems very
cumbersome. Any suggestions are greatly appreciated.

OpenVMS V7.3-2
Quiz V7.10.G4

Thank you,
Kristina Carlton
Senior Programmer/Analyst
The JI Companies
512.427.2459
kristina.carlton@jicompanies.com

------_=_NextPart_001_01C4C74E.85D63100
Content-Type: text/html
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:o =3D=20
"urn:schemas-microsoft-com:office:office" xmlns:w =3D=20
"urn:schemas-microsoft-com:office:word"><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<TITLE>Message</TITLE>

<META content=3DWord.Document name=3DProgId>
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR>
<META content=3D"Microsoft Word 9" name=3DOriginator><LINK=20
href=3D"cid:filelist.xml@01C4C717.FFA364D0" rel=3DFile-List><!--[if gte =
mso 9]><xml>
 <o:OfficeDocumentSettings>
  <o:DoNotRelyOnCSS/>
 </o:OfficeDocumentSettings>
</xml><![endif]--><!--[if gte mso 9]><xml>
 <w:WordDocument>
  <w:View>Normal</w:View>
  <w:Zoom>0</w:Zoom>
  <w:DocumentKind>DocumentEmail</w:DocumentKind>
  <w:EnvelopeVis/>
  <w:DrawingGridHorizontalSpacing>2.85 =
pt</w:DrawingGridHorizontalSpacing>
  =
<w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery>=

 </w:WordDocument>
</xml><![endif]-->
<STYLE>@font-face {
	font-family: Century Gothic;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; =
mso-header-margin: .5in; mso-footer-margin: .5in; mso-paper-source: 0; =
}
P.MsoNormal {
	FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; =
mso-bidi-font-size: 12.0pt; mso-style-parent: ""; mso-pagination: =
widow-orphan; mso-fareast-font-family: "Times New Roman"; =
mso-bidi-font-family: "Courier New"
}
LI.MsoNormal {
	FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; =
mso-bidi-font-size: 12.0pt; mso-style-parent: ""; mso-pagination: =
widow-orphan; mso-fareast-font-family: "Times New Roman"; =
mso-bidi-font-family: "Courier New"
}
DIV.MsoNormal {
	FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; =
mso-bidi-font-size: 12.0pt; mso-style-parent: ""; mso-pagination: =
widow-orphan; mso-fareast-font-family: "Times New Roman"; =
mso-bidi-font-family: "Courier New"
}
P.MsoAutoSig {
	FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; =
mso-bidi-font-size: 12.0pt; mso-pagination: widow-orphan; =
mso-fareast-font-family: "Times New Roman"; mso-bidi-font-family: =
"Courier New"
}
LI.MsoAutoSig {
	FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; =
mso-bidi-font-size: 12.0pt; mso-pagination: widow-orphan; =
mso-fareast-font-family: "Times New Roman"; mso-bidi-font-family: =
"Courier New"
}
DIV.MsoAutoSig {
	FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; =
mso-bidi-font-size: 12.0pt; mso-pagination: widow-orphan; =
mso-fareast-font-family: "Times New Roman"; mso-bidi-font-family: =
"Courier New"
}
SPAN.EmailStyle15 {
	COLOR: #333399; mso-bidi-font-family: Arial; mso-style-type: =
personal-compose; mso-ansi-font-size: 9.0pt; mso-ascii-font-family: =
"Century Gothic"; mso-hansi-font-family: "Century Gothic"
}
DIV.Section1 {
	page: Section1
}
</STYLE>
</HEAD>
<BODY lang=3DEN-US style=3D"tab-interval: .5in">
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>&gt; define d-last =
date =3D=20
date(days(round(sysdate,-2) + 1) - 1)<BR>&gt; define d-first date =3D=20
round(d-last,-2) + 1<BR></FONT><FONT face=3DArial color=3D#0000ff=20
size=3D2></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>&nbsp;</DIV></FONT>
<DIV><SPAN class=3D327534217-10112004><FONT face=3DArial =
color=3D#0000ff size=3D2>Of=20
course it may need to be benchmarked with a substring extract approach=20
:-)</FONT></SPAN></DIV>
<DIV><SPAN class=3D327534217-10112004><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D327534217-10112004><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>Regards</FONT></SPAN></DIV>
<DIV><SPAN class=3D327534217-10112004><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>Dave</FONT></SPAN></DIV>
<DIV><SPAN class=3D327534217-10112004><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr 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>Kristina Carlton<BR><B>Sent:</B> Wednesday, November =
10, 2004=20
  11:26 AM<BR><B>To:</B> 'powerh-l@lists.sowder.com'<BR><B>Subject:</B> =

  beginning/end of month<BR><BR></FONT></DIV>
  <DIV class=3DSection1>
  <P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century =
Gothic"=20
  color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><SPAN=20
  style=3D"mso-bidi-font-size: 12.0pt">Hi=20
  List!<o:p></o:p></SPAN></SPAN></FONT></SPAN></P>
  <P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century =
Gothic"=20
  color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><![if =
!supportEmptyParas]><![endif]>&nbsp;</SPAN><o:p></o:p></FONT></SPAN></P>=

<SPAN=20
 style=3D"mso-bidi-font-size: 12.0pt">I was wondering if someone can =
tell me the=20
 most efficient way to derived the beginning and end of month. =
We are=20
 running an extract on the first of every month and the date =
parameters need to=20
 be from the 1st through the last day of the =
previous month.=20
 I know there is a MONTH function as well as LASTDAY, DATEEXTRACT, =
etc. but=20
 deriving and converting the dates seems very cumbersome. Any =
suggestions are=20
 greatly appreciated.<o:p></o:p>
 <![if =
!supportEmptyParas]><![endif]>&nbsp;<o:p></o:p>=

  <P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century =
Gothic"=20
  color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><SPAN=20
  style=3D"mso-bidi-font-size: 12.0pt">OpenVMS=20
  V7.3-2<o:p></o:p></SPAN></SPAN></FONT></SPAN></P>
  <P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century =
Gothic"=20
  color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><SPAN=20
  style=3D"mso-bidi-font-size: 12.0pt">Quiz=20
  V7.10.G4<o:p></o:p></SPAN></SPAN></FONT></SPAN></P>
  <P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century =
Gothic"=20
  color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><![if =
!supportEmptyParas]><![endif]>&nbsp;</SPAN><o:p></o:p></FONT></SPAN></P>=

  <P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century =
Gothic"=20
  color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><SPAN=20
  style=3D"mso-bidi-font-size: 12.0pt">Thank=20
  you,<o:p></o:p></SPAN></SPAN></FONT></SPAN></P>
  <P class=3DMsoAutoSig><!--[if supportFields]><font =
color=3D"#333399"><span=20
style=3D'color:#333399'><span =
style=3D'mso-element:field-begin'></span><span=20
style=3D"mso-spacerun: yes">&nbsp;</span>AUTOTEXTLIST \s &quot;E-mail=20
Signature&quot; <span =
style=3D'mso-element:field-separator'></span></span></font><![endif]--><=
FONT=20
  color=3D#333399><SPAN style=3D"COLOR: #333399">Kristina =
Carlton</SPAN></FONT><FONT=20
  color=3D#333399><SPAN=20
  style=3D"COLOR: #333399; mso-color-alt: =
windowtext"><o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoAutoSig><FONT face=3D"Century Gothic" color=3D#333399 =
size=3D1><SPAN=20
  style=3D"FONT-SIZE: 9pt; COLOR: #333399"><SPAN=20
  style=3D"mso-bidi-font-size: 12.0pt">Senior=20
  Programmer/Analyst</SPAN></SPAN></FONT><FONT color=3D#333399><SPAN=20
  style=3D"COLOR: #333399; mso-color-alt: =
windowtext"><o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoAutoSig><FONT face=3D"Century Gothic" color=3D#333399 =
size=3D1><SPAN=20
  style=3D"FONT-SIZE: 9pt; COLOR: #333399"><SPAN=20
  style=3D"mso-bidi-font-size: 12.0pt">The JI =
Companies</SPAN></SPAN></FONT><FONT=20
  color=3D#333399><SPAN=20
  style=3D"COLOR: #333399; mso-color-alt: =
windowtext"><o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoAutoSig><FONT face=3D"Century Gothic" color=3D#333399 =
size=3D1><SPAN=20
  style=3D"FONT-SIZE: 9pt; COLOR: #333399"><SPAN=20
  style=3D"mso-bidi-font-size: =
12.0pt">512.427.2459</SPAN></SPAN></FONT><FONT=20
  color=3D#333399><SPAN=20
  style=3D"COLOR: #333399; mso-color-alt: =
windowtext"><o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoAutoSig><FONT face=3D"Century Gothic" color=3D#333399 =
size=3D1><SPAN=20
  style=3D"FONT-SIZE: 9pt; COLOR: #333399"><SPAN=20
  style=3D"mso-bidi-font-size: =
12.0pt">kristina.carlton@jicompanies.com</SPAN></SPAN></FONT><FONT=20
  color=3D#333399><SPAN=20
  style=3D"COLOR: #333399; mso-color-alt: =
windowtext"><o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><!--[if supportFields]><font =
color=3D"#333399"><span=20
style=3D'color:#333399'><span =
style=3D'mso-element:field-end'></span></span></font><![endif]--><FONT=20
  color=3D#333399><SPAN style=3D"COLOR: #333399"><![if =
!supportEmptyParas]><![endif]>&nbsp;</SPAN></FONT><FONT=20
  color=3D#333399><SPAN=20
  style=3D"COLOR: #333399; mso-color-alt: =
windowtext"><o:p></o:p></SPAN></FONT></P></DIV></BLOCKQUOTE></BODY></HTM=
L>

------_=_NextPart_001_01C4C74E.85D63100--