<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";}
p.code0, li.code0, div.code0
{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;}
span.emailstyle21
{font-family:Tahoma;
color:blue;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
span.EmailStyle23
{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'>You didn’t say whether you were
using A2K or A2002. If the latter, go to the queries tab and create a new
function. Paste the code I supplied in and save it. If A2K, then you need to
open Query Analyzer and do the same thing there.</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 12:16 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: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 color=blue
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma;color:blue'>Arthur
–</span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><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 style='margin-left:36.0pt'><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 style='margin-left:36.0pt'><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:36.0pt'><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 style='margin-left:36.0pt'><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 style='margin-left:36.0pt'><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:72.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> 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:72.0pt'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:72.0pt'><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:72.0pt'><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:72.0pt'><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:72.0pt;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:72.0pt;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:72.0pt;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:72.0pt;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:72.0pt;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:72.0pt;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:72.0pt;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:72.0pt;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:72.0pt;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:72.0pt;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:72.0pt'><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:72.0pt'><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:72.0pt'><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:72.0pt'><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:72.0pt'><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:72.0pt'><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:72.0pt'><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:108.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:108.0pt'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:108.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:108.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:108.0pt'><font size=1 face=Tahoma><span
style='font-size:8.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal style='margin-left:108.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:108.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:108.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:108.0pt'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoNormal style='margin-left:108.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:108.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:108.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:108.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:108.0pt'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'> </span></font></p>
<p class=MsoAutoSig style='margin-left:108.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:108.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:108.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:108.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:108.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:108.0pt'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'> </span></font></p>
</div>
</body>
</html>