Invalid Column Name

• charlene_wilson

  SSC Enthusiast

  Points: 117

  More actions

  November 6, 2007 at 1:19 pm

  #181020

  Am having problems with the following code. Trying to return the month and year portions of the date_started field and am getting the error "Invalid column name 'date_started'" Anyone have any suggestions as to how to fix this issue. Any help would be greatly appreciated.

  Thanks!

  DECLARE @DateMonth as int

  DECLARE @DateYear as int

  DECLARE @FiscalJulianDate as datetime

  SET @DateMonth = datename(mm, date_started)

  SET @DateYear = datepart(yyyy, date_started)

  IF (@DateMonth >= 7 and @DateMonth <= 12)

  SET @FiscalJulianDate = CONVERT(datetime, Str(@DateYear)+'-07-01', 110)

  ELSE

  SET @FiscalJulianDate = CONVERT(datetime, Str(@DateYear - 1)+'-07-01', 110)

  SELECT

  CONVERT(datetime, Str(datepart(yyyy, date_started /*@DateYear*/))+'-07-01', 110) AS Date1,

  CONVERT(datetime, Str(datepart(yyyy, date_started /*@DateYear*/) - 1)+'-07-01', 110) AS Date2,

  pja.emp_no,

  pja.date_started

  FROM

  personnel_job_assoc pja

  WHERE

  pja.emp_no = 6111

• Johannes Fourie

  Default port

  Points: 1470

  More actions

  November 6, 2007 at 1:34 pm

  #748840

  Hi, no table is referenced in these 2 SET statements:

  SET @DateMonth = datename(mm, date_started)

  SET @DateYear = datepart(yyyy, date_started)

  So SQL cant find the column name date_started.

  Use a select statement to get the values:

  SELECT @DateMonth = datename(mm, date_started), @DateYear = datepart(yyyy, date_started)

  FROM

  personnel_job_assoc pja

  WHERE

  pja.emp_no = 6111

  I presumed the data is coming from the personnel_job_assoc table