need help with assigning a value to a variable

• water490

  SSC Eights!

  Points: 972

  More actions

  December 25, 2024 at 7:33 pm

  Go to Answer

  #4504751

  Hi

  I am trying to assign the value of the  variable using the output of a query but I am getting an error.  How do I fix it?

  Query:

  DECLARE @LATEST_DATE DATE;
  
  SET @LATEST_DATE = SELECT MAX(MY_DATE) FROM DBO.TABLE

  Error:

  Msg 156, Level 15, State 1, Line 8
  Incorrect syntax near the keyword 'SELECT'.

  Thank you

• ScottPletcher

  SSC Guru

  Points: 101124

  More actions

  December 25, 2024 at 7:36 pm

  Answer

  #4504752

  Either below; the first  is more typical:

  DECLARE @LATEST_DATE DATE;
  
  SELECT @LATEST_DATE = MAX(MY_DATE) FROM DBO.TABLE
  
  --or:
  
  SET @LATEST_DATE = (SELECT MAX(MY_DATE) FROM DBO.TABLE);

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• water490

  SSC Eights!

  Points: 972

  More actions

  December 25, 2024 at 7:40 pm

  #4504753

  ScottPletcher wrote:

  Either below; the first  is more typical:

  DECLARE @LATEST_DATE DATE;
  
  SELECT @LATEST_DATE = MAX(MY_DATE) FROM DBO.TABLE
  
  --or:
  
  SET @LATEST_DATE = (SELECT MAX(MY_DATE) FROM DBO.TABLE);

    It works.  Thank you so much!