Converting INT to DATE then using GETDATE on conversion?

Question

I am trying to convert the results from an INT column to DATE so the GETDATE function will be compatible with this column. The date is currently in the format yyyymmdd

This is what I have so far based on what I could find but I am sure it is completely wrong

...AND (dbo.V_HEAD.LF_DATE CONVERT(DATE,(CONVERT(INT, LF_DATE)) >= GETDATE-28)

AND (dbo.V_HEAD.LF_DATE CONVERT(DATE,(CONVERT(INT, LF_DATE)) <= GETDATE)...

I want the results qualified on LF_DATE for the last 28 days too

The rest of the script runs correctly. Where am I going wrong and how can I correct it?

Answer 1

@Zohar Peled, I think I have cracked it! It is subtracting 28 as an int and not days. The problem is 20160809 - 28 = 20160781 which is no good

The desired results would be

SELECT * FROM @T WHERE LF_DATE >= @DateAsInt - 28 (DAYS) AND LF_DATE <= @DateAsInt

Id ActualDate LF_Date

---

10 2016-08-09 20160809 9 2016-08-04 20160804 8 2016-07-30 20160730 7 2016-07-25 20160725 6 2016-07-20 20160720 5 2016-07-15 20160715

As 20160809 - 28 DAYS would include dates from 20160712

The way around this was to subtract 97 instead of 28.

This this is not very clean, there must be a better way...

Answer 2

Update
Following your comments, I've created some sample data to test my answer:
Create and populate sample data (Please save us this step in your future questions)

DECLARE @T as TABLE
(
    Id int,
    ActualDate Date,
    LF_Date int
)

INSERT INTO @T (Id, ActualDate) VALUES
(10, DATEADD(DAY, -5, GETDATE())),
(9, DATEADD(DAY, -10, GETDATE())),
(8, DATEADD(DAY, -15, GETDATE())),
(7, DATEADD(DAY, -20, GETDATE())),
(6, DATEADD(DAY, -25, GETDATE())),
(5, DATEADD(DAY, -30, GETDATE())),
(4, DATEADD(DAY, -35, GETDATE())),
(3, DATEADD(DAY, -40, GETDATE())),
(2, DATEADD(DAY, -45, GETDATE())),
(1, DATEADD(DAY, -50, GETDATE()))

UPDATE @T 
SET LF_Date = YEAR(ActualDate) * 10000 + MONTH(ActualDate) * 100 + DAY(ActualDate)

Test sample data:

SELECT *
FROM @T 

Results:

Id          ActualDate  LF_Date
----------- ----------  -----------
10          2016-08-09  20160809
9           2016-08-04  20160804
8           2016-07-30  20160730
7           2016-07-25  20160725
6           2016-07-20  20160720
5           2016-07-15  20160715
4           2016-07-10  20160710
3           2016-07-05  20160705
2           2016-06-30  20160630
1           2016-06-25  20160625

As you can see, the sample table's LF_Date column is an int that keeps the date as yyyyMMdd, just like in the question.

The query:

DECLARE @DateAsInt int,
        @Date date = GETDATE();

SELECT @DateAsInt = YEAR(@Date) * 10000 + MONTH(@Date) * 100 + DAY(@Date);

SELECT *
FROM @T 
WHERE LF_DATE >= @DateAsInt - 28
AND LF_DATE <= @DateAsInt

Results:

Id          ActualDate  LF_Date
----------- ----------  -----------
10          2016-08-09  20160809
9           2016-08-04  20160804

Conclusion:

as far as the sample data goes, the answer is fine. You need to test your data to see what's stopping you from getting the results from the previous month, but I seriously doubt that it's my suggestion.

First version
Assuming your Sql server version is 2012 or higher, you can use some math and the DATEFROMPARTS built in function:

DECLARE @IntDate int = 20160322

SELECT  DATEFROMPARTS (
            (@IntDate - (@IntDate % 10000)) / 10000,
            (@IntDate % 1000) / 100,
            @IntDate % 100
        ) As [Date]

Results:

Date
2016-03-22

However, It will be simpler and probably have a better performance to convert the date to int:

DECLARE @Date date = '2016-03-22'

SELECT  YEAR(@Date) * 10000 + 
        MONTH(@Date) * 100 + 
        DAY(@Date) As [Int]

Results:

Int
20160322

To put that in context of your question - calculate the int value of the current date before your query:

DECLARE @DateAsInt int,
        @Date date = GETDATE();
    
SELECT @DateAsInt = YEAR(@Date) * 10000 + MONTH(@Date) * 100 + DAY(@Date);

And then, in your where clause you simply write this:

...
AND LF_DATE >= @DateAsInt - 28
AND LF_DATE <= @DateAsInt
...

In any case, you will be better off if you could change your table structure and replace that int column with a date column. Read Aaron Bertrand's Bad habits to kick : choosing the wrong data type.

Answer 3

Perhaps this may help

Select DateAdd(DD,-28,cast(cast(20160809 as varchar(8)) as date))

Returns 2016-07-12

However, since your data is an int, I think it would be more efficient to convert the desired date range into an int rather than performing row level calculations

Declare @DateR1 int = Format(DateAdd(DD,-28,GetDate()),'yyyyMMdd')
Declare @DateR2 int = Format(GetDate(),'yyyyMMdd')

Select DateR1=@DateR1,DateR2=@DateR2

Returns

DateR1      DateR2
20160712    20160809