Select Records based on Year

• Chris.Hubbard4U

  Ten Centuries

  Points: 1020

  More actions

  August 17, 2015 at 6:14 am

  #302027

  DECLARE @Date

  SET @Date = 2012

  DECLARE @Year int

  SET @Year = (SELECT DATEPART(yyyy,@Date))

  SELECT @Year AS Year

  --SELECT *

  -- FROM [Orders].[dbo].[Orders] od

  -- WHERE .Orderl_Date < @Date;

  ----WHERE DATEPART(yyyy,@Date)= @Year

  ----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 17, 2015 at 6:18 am

  #1820405

  Duplicate post. No replies please

  Replies to http://www.sqlservercentral.com/Forums/Topic1712220-3077-1.aspx

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 17, 2015 at 6:19 am

  #1820406

  you want to leave the column alone, without functions or conversions, so you can use any existing indexes.

  instead, generate the dates the date values should be in between:

  DECLARE @Date datetime --use a proper date!

  SET @Date =getdate() --august 17 of this year

  SELECT *

  FROM [Orders].[dbo].[Orders] od

  WHERE Orderl_Date >= DATEADD(yy, DATEDIFF(yy,0,@Date), 0) --jan 1 of the year of the date param passed.

  AND Orderl_Date < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@Date), 0)) --add one year: jan 1 of following year

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  August 17, 2015 at 6:21 am

  #1820407

  Chris.Hubbard4U (8/17/2015)

  ---

  DECLARE @Date

  SET @Date = 2012

  DECLARE @Year int

  SET @Year = (SELECT DATEPART(yyyy,@Date))

  SELECT @Year AS Year

  --SELECT *

  -- FROM [Orders].[dbo].[Orders] od

  -- WHERE .Orderl_Date < @Date;

  ----WHERE DATEPART(yyyy,@Date)= @Year

  ----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear

  Here's one way:

  declare @Year int = 2012

  declare @StartDate date = datefromparts(@Year, 1, 1)

  declare @EndDate date = datefromparts(@Year + 1, 1, 1)

  select

  from

  where [date] >= @StartDate and [date] < @EndDate

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  August 17, 2015 at 6:22 am

  #1820409

  GilaMonster (8/17/2015)

  ---

  Duplicate post. No replies please

  Replies to http://www.sqlservercentral.com/Forums/Topic1712220-3077-1.aspx

  Oops, didn't see this in time.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Chris.Hubbard4U

  Ten Centuries

  Points: 1020

  More actions

  August 17, 2015 at 7:53 am

  #1820436

  GilaMonster (8/17/2015)

  ---

  Duplicate post. No replies please

  Replies to http://www.sqlservercentral.com/Forums/Topic1712220-3077-1.aspx

  Sorry it was an accident.:blush: