February 20, 2018 at 4:38 am
Guys, if i have a list of dates in a table how do i query how many of the dates are 5 years or older?
February 20, 2018 at 4:42 am
craig.jenkins - Tuesday, February 20, 2018 4:38 AMGuys, if i have a list of dates in a table how do i query how many of the dates are 5 years or older?
DATE functions could be used for your filtration.
February 20, 2018 at 5:09 am
Create your WHERE something like:
WHERE [your_date_column] > DATEADD (year, -5 , GETDATE() )
Try to not wrap the function around your column so the query can still use any indexes on that column.
February 20, 2018 at 5:13 am
craig.jenkins - Tuesday, February 20, 2018 4:38 AMGuys, if i have a list of dates in a table how do i query how many of the dates are 5 years or older?
Do you want five years ago today, or 1/1/five years ago? Is your date column actually a datetime column, in which case you might want to 'set' the time to 00:00.
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
February 20, 2018 at 5:26 am
Thank Guys, i've attached some dummy data below. As you can see the regdates are not older than 5 years
-- Sample data
IF OBJECT_ID('tempdb..#LOGFILE') IS NOT NULL DROP TABLE #LOGFILE
SELECT * INTO #LOGFILE FROM (VALUES
('BAA', '2018-02-19 10:53:08.837','2016-03-31 00:00:00.000','123451'),
('BAA', '2018-02-19 10:53:08.837','2015-02-28 00:00:00.000','123452')
) d (Dealer, Created, Regdate, RegNo)
SELECT DISTINCT * FROM #LOGFILE
WHERE #LOGFILE.Dealer = 'BAA'--
AND #LOGFILE.Created >='2018-02-19' AND #LOGFILE.Created <'2018-02-20'
AND #LOGFILE.RegdATE > DATEADD (year, -5 , GETDATE() )
February 20, 2018 at 5:46 am
Thank Guys, i've attached some dummy data below. As you can see the regdates are not older than 5 years
And that is why you are selecting them.
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
February 20, 2018 at 5:50 am
I'm an idiot, sorry guys
February 20, 2018 at 5:57 am
craig.jenkins - Tuesday, February 20, 2018 5:50 AMI'm an idiot, sorry guys
Don't be so hard on yourself, we've all done it 🙂
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply