Data Older than 12 month

Question

Data Older than 12 month

Nita Reddy

SSCarpal Tunnel

Points: 4182
More actions
March 5, 2020 at 2:46 pm

#3731431

I have date which is stored in Integer how do I get data older than 12 months
select * from table1
where data = 20190131 (This is how the date is stored in table as Integer)
June 23, 2020 at 5:49 am

This was removed by the editor as SPAM

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Something like this, which isn't very efficient. Better to stored your dates as dates if that's an option.

WHERE DATEADD(MONTH,12,CAST(CAST(data AS char(8)) AS date)) < GETDATE()

John

Nita Reddy SSCarpal Tunnel Points: 4182 More actions · Answer 2

Thanks I am able to get my result.

This is how the application has setup date unfortunately

Thanks Once again

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

You can potentially shortcut John's method. If you add or subtract 10000 to an INT date held as YYYYMMDD, you are effectively adding or subtracting a year.

(If one of your dates is 29 Feb, this method does not always work.)

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: 246986 More actions · Answer 4

John Mitchell-245523 wrote:

Something like this, which isn't very efficient. Better to stored your dates as dates if that's an option.
WHERE DATEADD(MONTH,12,CAST(CAST(data AS char(8)) AS date)) < GETDATE()
John

John, I think it would be faster to convert GETDATE() rather than the data. Something like this

DECLARE @D1 INT = CAST(CONVERT(VARCHAR(10), DATEADD(YEAR, -1, GETDATE()), 112) AS INT)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

John Mitchell-245523

SSC Guru

Points: 148809

March 5, 2020 at 3:28 pm

#3731462

Yes, that would be better. Thanks Phil.

John