October 4, 2012 at 3:36 am
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
October 4, 2012 at 3:43 am
Select LTRating
,RHDate
from Accounts
WHERE LTRating <> 'D' OR RHDate = DATEADD(DD,-365,GETDATE())
October 4, 2012 at 3:45 am
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
October 4, 2012 at 5:32 am
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,...)
October 4, 2012 at 5:38 am
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)))
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
October 5, 2012 at 4:16 am
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/
October 5, 2012 at 4:58 am
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
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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy