<html>
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
h2
{margin-top:12.0pt;
margin-right:0in;
margin-bottom:3.0pt;
margin-left:0in;
page-break-after:avoid;
font-size:14.0pt;
font-family:Arial;
font-style:italic;}
h3
{margin-top:12.0pt;
margin-right:0in;
margin-bottom:3.0pt;
margin-left:0in;
page-break-after:avoid;
font-size:13.0pt;
font-family:Arial;}
h4
{margin-top:3.0pt;
margin-right:0in;
margin-bottom:3.0pt;
margin-left:0in;
page-break-after:avoid;
font-size:12.0pt;
font-family:Arial;}
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:12.0pt;
font-family:"Times New Roman";}
p.code, li.code, div.code
{margin-top:6.0pt;
margin-right:0in;
margin-bottom:6.0pt;
margin-left:17.85pt;
punctuation-wrap:simple;
text-autospace:none;
font-size:12.0pt;
font-family:"Courier New";}
span.emailstyle17
{font-family:Tahoma;
color:windowtext;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
span.emailstyle20
{font-family:Arial;
color:navy;}
span.EmailStyle21
{font-family:Tahoma;
color:blue;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>Arthur –</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>Please excuse my ignorance when it comes
to sprocs.</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>Where do I create the JustDate function?</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'> </span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>Thanks,</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'>Mark</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma;color:blue'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b> Arthur Fuller
[mailto:artful@rogers.com] <br>
<b><span style='font-weight:bold'>Sent:</span></b> Tuesday, February 11, 2003
12:08 PM<br>
<b><span style='font-weight:bold'>To:</span></b>
dba-sqlserver@databaseadvisors.com<br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: [dba-SQLServer]Update
sproc</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>I think that it’s
the call to Now() that is confusing SQL. The error points to INNER JOIN because
that’s the clause in which the error occurs. Change the call from
Int(Now()) to GetDate(). Since you int()’d the date presumably you
don’t want the time part. Here is a udf that simplifies this:</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>ALTER FUNCTION
dbo.fn_JustDate</span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'> ( @Date
datetime )</span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>RETURNS
varchar(10)</span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>AS</span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>BEGIN</span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'> RETURN (
CONVERT(varchar(10),@Date,101) )</span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>END</span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>Then you can
change the last part to</span></font></p>
<p class=MsoNormal style='margin-left:.5in;text-autospace:none'><font size=1
face=Tahoma><span style='font-size:8.5pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>SET tblCharges.PaidCode =
‘CP’, tblCharges.PaidDate = JustDate(GetDate())</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>I think that should work.</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Hth,</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Arthur</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b>
dba-sqlserver-admin@databaseadvisors.com [mailto:dba-sqlserver-admin@databaseadvisors.com]
<b><span style='font-weight:bold'>On Behalf Of </span></b>Mark Boyd<br>
<b><span style='font-weight:bold'>Sent:</span></b> February 11, 2003 11:43 AM<br>
<b><span style='font-weight:bold'>To:</span></b> SQLServerList<br>
<b><span style='font-weight:bold'>Subject:</span></b> [dba-SQLServer]Update
sproc</span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>I am trying to create a sproc in
SQL2K that will update records.</span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>I have the following code:</span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=1 face=Tahoma><span
style='font-size:8.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=1 face=Tahoma><span
style='font-size:8.0pt;font-family:Tahoma'>UPDATE tblCheckRequest </span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=1 face=Tahoma><span
style='font-size:8.0pt;font-family:Tahoma'>INNER JOIN tblCharges ON
(tblCheckRequest.ClaimNo = tblCharges.ClaimNo) AND (tblCheckRequest.EndServDate
= tblCharges.EndServDate) AND (tblCheckRequest.CPT = tblCharges.CPT) AND
(tblCheckRequest.DupCode = tblCharges.DupCode) AND (tblCheckRequest.ProvNo = tblCharges.ProvNo)
AND (tblCheckRequest.PatientNo = tblCharges.PatientNo) </span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=1 face=Tahoma><span
style='font-size:8.0pt;font-family:Tahoma'>SET tblCharges.PaidCode = 'CP',
tblCharges.PaidDate = Int(Now());</span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>When running this code, I receive
the following message:</span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>“Incorrect syntax near the
keyword ‘INNER’”.</span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Should I not be using ‘INNER
JOIN ..’?</span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Very confused.</span></font></p>
<p class=MsoAutoSig style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoAutoSig style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Any help is greatly appreciated.</span></font></p>
<p class=MsoAutoSig style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Thanks,</span></font></p>
<p class=MsoAutoSig style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Mark Boyd</span></font></p>
<p class=MsoAutoSig style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Sr. Systems Analyst</span></font></p>
<p class=MsoAutoSig style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>McBee Associates, Inc.</span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
</body>
</html>