How to convert tbl_date(int) to date in SQL ?

Question

I have a transaction table that has the date column saved as an INT in the format "yyyymmdd".

I need to run a query that when refreshed only pulls the last 7 days worth of data regardless of when it is run.

I tried using date_parse:

date_parse(cast(tbl_date as varchar), '%Y%m%d') as Date

but when I use the:

where tbl_date >= date(current_date) - interval '7' day

i get an error.

Please help

Answer 1

Try one of solutions:

select *
from TransactionTable
where tbl_date >= convert(char(8), dateadd(day, -7, getdate()), 112)

Answer 2

Hi @Zaahid Munshi,

Please try along the following.

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, bogus_date INT);
INSERT @tbl (bogus_date) VALUES
(20230905),
(20230831);
-- DDL and sample data population, end

SELECT * 
	, real_date = TRY_CAST(CAST(bogus_date AS CHAR(8)) AS DATE)
FROM @tbl;

Answer 3

sqlserver does not support current_date, date_parse or date - interval. on sqlserver the above would be:

where cast(cast(tbl_date as varchar(20)) as date) >= dateadd(day, -7, getdate())

this of course requires a table scan. a better might be to convert today -7 to an int:

where tbl_date >= datepart(yyyy, dateadd(day,-7,getdate())) * 10000 
    + datepart(mm, dateadd(day,-7,getdate())) * 100 
    + datepart(day, dateadd(day,-7,getdate()))

Answer 4

Hi @Zaahid Munshi

I tried using date_parse:

As answered above, neither date_parse nor current_date is supported in SQL Server.

You could use format function to format the int value.

I need to run a query that when refreshed only pulls the last 7 days worth of data regardless of when it is run.

You could use DATEDIFF along with GETDATE() to calculate date gap.

Please check this query:

SELECT * FROM YourTable
WHERE DATEDIFF(DAY,format(tbl_date,'####-##-##'),GETDATE())<=7

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.