need a second set of eyes

Results 1 to 3 of 3

Thread: need a second set of eyes

  1. #1
    Join Date
    Dec 1969

    Default need a second set of eyes

    have a simple stor proc that does couple insert, the 1st insert has no errors but the second one is really torturing me with " Incorrect syntax near keyword convert" error<BR><BR>been messing for this for more than 8 hours, appreciate any help <BR>below is the code<BR>-----------------<BR><BR>ALTER procedure dbo.InsertRecordsProc <BR>@prj_name varchar(100), -- for tblCore<BR>@up_by varchar(100), -- for tblCore<BR>@intStatus int, <BR>@intBudget int, <BR>@intScope int, <BR>@intQuality int, <BR>@intValue int, <BR>@intActivity int, <BR>@sSubmitDate datetime, <BR>@sEstDate datetime, <BR>@sCommitDate datetime, <BR>@sActualDate datetime <BR><BR>AS<BR>begin<BR>-- declare variables for storing current id and updated date<BR>declare @current_id int<BR>declare @Latest_Updated_Date datetime<BR><BR>-- get latest id<BR>select @current_id = (select current_id from dbo.tblSequence)<BR><BR>-- insert values into tblCore<BR>INSERT INTO [ADC_PMO].[dbo].[tblCore] <BR>([prj_id], [Project_Name],[Updated_Last_by],[Updated_Date])<BR>VALUES(@current_id, @prj_name, @up_by, getdate())<BR><BR>-- retrieve updated date from above insert<BR>SELECT @Latest_Updated_Date = (select c.Updated_Date from tblCore c <BR>where<BR>(c.Prj_ID = @current_id) AND <BR>(c.Updated_Date = (select max(c1.updated_date) from tblCore c1 <BR>where c1.Prj_ID = c.Prj_ID)))<BR><BR>-- insert values into tblProj<BR>INSERT INTO <BR>dbo.tblProject([prj_id],[status_id],[budget_id],[scope_id],[quality_id],[value_id],[activity_id],[Submission_Date],[Est_complete_Date],[commit_complete_date],[Actual_Complete_Date],[Updated_Date]) <BR>VALUES <BR>(@current_id, <BR>@intStatus, <BR>@intBudget, <BR>@intScope, <BR>@intQuality, <BR>@intValue, <BR>@intActivity,<BR>@sSubmitDate,<BR>@sEstDate, <BR>@sCommitDate, <BR>@sActualDate, <BR>@Latest_Updated_Date)<BR><BR><BR>end <BR>GO<BR><BR><BR>dbo.InsertRecordsProc &#039;xzzcxxc&#039;, &#039;susan&#039;, NULL, 0, 0, 0, 0, 1, <BR>convert(datetime,&#039;14 Aug 2004&#039;,106), convert(datetime,NULL,106), <BR>convert(datetime,NULL,106), convert(datetime,NULL,106)<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: need a second set of eyes

    Try this instead:<BR><BR>[code language="T-SQL"]<BR>DECLARE @Date1 DATETIME<BR>SET @Date1 = CONVERT(DATETIME,&#039;14 Aug 2004&#039;,106)<BR>EXEC dbo.InsertRecordsProc &#039;xzzcxxc&#039;, &#039;susan&#039;, NULL, 0, 0, 0, 0, 1, @Date1, NULL, NULL, NULL<BR>[/code]

  3. #3
    Join Date
    Dec 1969

    Default You can't use CONVERT ...

    ...with a NULL value. And there is no need to do so, anyway. A NULL is a NULL is a NULL, no matter what the data type.<BR><BR>Which is what S.R.G. was showing you, but I thought I&#039;d try explaining what he did.<BR><BR>If your example call of InsertRecordsProc was indeed just an example, then you could do:<BR><BR> ... CASE WHEN @someParam IS NULL THEN NULL ELSE Convert(DATETIME,@someParam,106) END ...<BR><BR>But I don&#039;t see why you need ANY convert in there. SQL Server *should* be able to handle the string format you are using just fine.<BR><BR>dbo.InsertRecordsProc &#039;xzzcxxc&#039;, &#039;susan&#039;, NULL, 0, 0, 0, 0, 1, <BR>&#039;14 Aug 2004&#039;, NULL, NULL, NULL<BR><BR>Finally, if what you want is today&#039;s date in the table for Submission_Date, you could do:<BR> CONVERT(DATETIME, CONVERT(VARCHAR(30), getDate(), 102), 102)<BR><BR>[I think 102 is right. You just want a format that gets *only* the date, extracted from the date and time the getDate() gives you.]<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts