Francisco H Tapia
my.lists at verizon.net
Tue Feb 11 12:36:19 CST 2003
Mark, Try this.. CONVERT(CHAR(12),GetDate(),110) -Francisco http://rcm.netfirms.com ----- Original Message ----- From: "Mark Boyd" <MarkBoyd at mcbeeassociates.com> To: <dba-sqlserver at databaseadvisors.com> Sent: Tuesday, February 11, 2003 10:29 AM Subject: RE: [dba-SQLServer]Update sproc : Arthur, Francisco, Doris - : Thanks for all your help. : I used a little from each of your replies to finally correct my issue. : : The sproc runs smoothly, updating the fields correctly. : However, I still can't get the date to display without the time. : I tried using Arthur's idea with a udf, but am getting errors. : Is there an easier way to shorten the GetDate() function? : : Thanks again, : Mark : : -----Original Message----- : From: Francisco H Tapia [mailto:my.lists at verizon.net] : Sent: Tuesday, February 11, 2003 12:10 PM : To: dba-sqlserver at databaseadvisors.com : Subject: Re: [dba-SQLServer]Update sproc : : Mark, : You can't just copy and paste Access code over to SqlServer. From : the QA : (Query Analyzer) you can select the keyword UPDATE and then just hit the : key : combo Shift + F1 and help will automatically be brought up for the : proper : syntax of how to use UPDATE in Books Online (BOL). I've taken the : liberty : to massage your sql statment, here is what I came up with, also note : that : Now() is an Access function not a Sql Server one thus it won't work, you : can : try using the GetDate() wich generates the date you want but not the : serial : number. Also INT() is not a valid vunction in Sql Server... I hope this : sets you in the right direction... : : UPDATE tblCheckRequest : SET tblCharges.PaidCode = 'CP', tblCharges.PaidDate = GetDate() : FROM tblCheckRequest : 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) : : -Francisco : http://rcm.netfirms.com : ----- Original Message ----- : From: "Mark Boyd" <MarkBoyd at McBeeAssociates.com> : To: "SQLServerList" <dba-SQLServer at databaseadvisors.com> : Sent: Tuesday, February 11, 2003 8:43 AM : Subject: [dba-SQLServer]Update sproc : : : : I am trying to create a sproc in SQL2K that will update records. : : : : I have the following code: : : : : : : : : UPDATE tblCheckRequest : : : : 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) : : : : SET tblCharges.PaidCode = 'CP', tblCharges.PaidDate = Int(Now()); : : : : : : : : When running this code, I receive the following message: : : : : "Incorrect syntax near the keyword 'INNER'". : : : : Should I not be using 'INNER JOIN ..'? : : : : Very confused. : : : : : : : : Any help is greatly appreciated. : : : : Thanks, : : : : Mark Boyd : : : : Sr. Systems Analyst : : : : McBee Associates, Inc. : : : : : : : : : : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com :