March 5, 2020 at 2:46 pm
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)
March 5, 2020 at 3:01 pm
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
March 5, 2020 at 3:09 pm
Thanks I am able to get my result.
This is how the application has setup date unfortunately
Thanks Once again
March 5, 2020 at 3:14 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 5, 2020 at 3:22 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 5, 2020 at 3:28 pm
Yes, that would be better. Thanks Phil.
John
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