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

Question

Post reply

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Davin21 SSC Eights! Points: 860 More actions · Answer 1

Select LTRating

,RHDate

from Accounts

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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 · Answer 6

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