get value from parameter

• girl_bj

  SSCommitted

  Points: 1731

  More actions

  June 14, 2019 at 7:24 am

  #3653661

  Hi,

  I am passing parameter in my store procedure.

  declare @year datetime= '2019'

  select * from table where sportyear=@year

  How can I extract where if i pass in '2019', my where condition sportyear will get last year which is 2018.

• Thom A

  SSC Guru

  Points: 99143

  More actions

  June 14, 2019 at 8:06 am

  #3653669

  2019 - 1 = 2018, so what wrong with WHERE sportyear = @year - 1; here?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• DesNorton

  SSC-Insane

  Points: 24540

  More actions

  June 14, 2019 at 8:17 am

  #3653670

  girl_bj wrote:

  Hi, I am passing parameter in my store procedure. declare @year datetime= '2019' select * from table where sportyear=@year How can I extract where if i pass in '2019', my where condition sportyear will get last year which is 2018.

   

  What is the datatype of sportyear in the table?

  If it is datetime, then your select will only bring back results where sportyear = '2019-01-01 00:00:00.000'.  Any other values in the year will be filtered out

  ---

  How to post data/code on a forum to get the best help.

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  June 14, 2019 at 1:50 pm

  #3653814

  Thom A wrote:

  2019 - 1 = 2018, so what wrong with WHERE sportyear = @year - 1; here?

  With @year being defined as an Integer rather than a datetime.

  ---

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  June 14, 2019 at 2:11 pm

  #3653822

  select * from table where sportyear=DATEADD(YEAR, -1, @year)

  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".