How to use GETDATE() function in sql server to select a column value from table whose date is only 1 year back from today.

• maida_rh

  SSC Eights!

  Points: 974

  More actions

  October 4, 2012 at 3:36 am

  #268751

  How to use GETDATE() function in sql server to select a column value from table whose date is only 1 year back from today.

  E.g :

  Select LTRating ,RHDate from Accounts and If LTRating ='D' then RHDate =CURRENTDATE()-365

  All LTRating column values and if LTRating value is 'D' the Date must be 1 year old only

• Davin21

  SSC Eights!

  Points: 880

  More actions

  October 4, 2012 at 3:43 am

  #1545421

  Select LTRating

  ,RHDate

  from Accounts

  WHERE LTRating <> 'D' OR RHDate = DATEADD(DD,-365,GETDATE())

• Ant-Green

  SSC Guru

  Points: 113544

  More actions

  October 4, 2012 at 3:45 am

  #1545423

  Something like the below

  SELECT

  LTRating,

  RHDate

  FROM

  Accounts

  WHERE

  LTRating = 'D'

  AND

  dateadd(dd, datediff(dd, 0, RHDate), 0) = dateadd(year,-1,dateadd(dd, datediff(dd, 0, GETDATE()), 0)) -- Removes the time element from any datetimes, so eg 1900-01-01 00:00:00.000

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  October 4, 2012 at 5:32 am

  #1545458

  maida_rh (10/4/2012)

  ---

  How to use GETDATE() function in sql server to select a column value from table whose date is only 1 year back from today.

  E.g :

  Select LTRating ,RHDate from Accounts and If LTRating ='D' then RHDate =CURRENTDATE()-365

  All LTRating column values and if LTRating value is 'D' the Date must be 1 year old only

  365?

  It's not going to work for all years

  You better do: DATEADD(YEAR, -1,...)

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 4, 2012 at 5:38 am

  #1545461

  maida_rh (10/4/2012)

  ---

  How to use GETDATE() function in sql server to select a column value from table whose date is only 1 year back from today.

  E.g :

  Select LTRating ,RHDate from Accounts and If LTRating ='D' then RHDate =CURRENTDATE()-365

  All LTRating column values and if LTRating value is 'D' the Date must be 1 year old only

  -- Sample data

  ;WITH Accounts AS (

  SELECT TOP 100

  LTRating = 'A',

  RHDate = DATEADD(day,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-50-365,GETDATE())

  FROM sys.columns a, sys.columns b

  )

  -- SARGable Solution

  SELECT

  LTRating,

  RHDate,

  Today = DATEADD(year,-1,CAST(GETDATE() AS DATE)),

  Tomorrow = DATEADD(day,1,DATEADD(year,-1,CAST(GETDATE() AS DATE)))

  FROM Accounts

  WHERE LTRating <> 'D'

  AND RHDate >= DATEADD(year,-1,CAST(GETDATE() AS DATE))

  AND RHDate < DATEADD(day,1,DATEADD(year,-1,CAST(GETDATE() AS DATE)))

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  October 5, 2012 at 4:16 am

  #1545977

  Hi maida,

  here is the query wch list out all the dates 1 year less than the current date..

  Select LTRating ,RHDate from Accounts and If LTRating ='D' then RHDate < DATEadd(YY,-1,getdate())

  Hope it works for you

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 5, 2012 at 4:58 am

  #1545988

  kapil190588 (10/5/2012)

  ---

  Hi maida,

  here is the query wch list out all the dates 1 year less than the current date..

  Select LTRating ,RHDate from Accounts and If LTRating ='D' then RHDate < DATEadd(YY,-1,getdate())

  Hope it works for you

  Run the following query to see why this solution won't work if RHDate is DATETIME:

  SELECT

  aYearAgoExactly,

  aYearAgoMinusFourHours,

  aYearAgoPlusFourHours,

  CASE WHEN aYearAgoExactly <= aYearAgoExactly THEN 'Yes' ELSE 'No' END,

  CASE WHEN aYearAgoMinusFourHours <= aYearAgoExactly THEN 'Yes' ELSE 'No' END,

  CASE WHEN aYearAgoPlusFourHours <= aYearAgoExactly THEN 'Yes' ELSE 'No' END

  FROM (

  SELECT

  aYearAgoExactly = DATEadd(YY,-1,getdate()),

  aYearAgoMinusFourHours= DATEADD(HOUR,-4,DATEadd(YY,-1,getdate())),

  aYearAgoPlusFourHours = DATEADD(HOUR,4,DATEadd(YY,-1,getdate()))

  ) d

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden