<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:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
h2
{margin-top:12.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
page-break-after:avoid;
font-size:14.0pt;
font-family:Arial;
font-style:italic;}
h3
{margin-top:12.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
page-break-after:avoid;
font-size:13.0pt;
font-family:Arial;}
h4
{margin-top:3.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
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:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
p.Code, li.Code, div.Code
{margin-top:6.0pt;
margin-right:0cm;
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;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><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><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><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='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='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='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='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='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='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='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='text-autospace:none'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal style='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='text-autospace:none'><font size=1 face=Tahoma><span
style='font-size:8.5pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal><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><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><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><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><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><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><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:36.0pt'><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:36.0pt'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><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:36.0pt'><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:36.0pt'><font size=1 face=Tahoma><span
style='font-size:8.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><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:36.0pt'><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:36.0pt'><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:36.0pt'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><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:36.0pt'><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:36.0pt'><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:36.0pt'><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:36.0pt'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoAutoSig style='margin-left:36.0pt'><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:36.0pt'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>Thanks,</span></font></p>
<p class=MsoAutoSig style='margin-left:36.0pt'><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:36.0pt'><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:36.0pt'><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:36.0pt'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></p>
</div>
</body>
</html>