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

Zaahid Munshi 0 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,002 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Viorel 102.8K Reputation points

    Try one of solutions:

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

  2. Yitzhak Khabinsky 23,321 Reputation points

    Hi @Zaahid Munshi,

    Please try along the following.

    -- DDL and sample data population, start
    INSERT @tbl (bogus_date) VALUES
    -- DDL and sample data population, end
    SELECT * 
    	, real_date = TRY_CAST(CAST(bogus_date AS CHAR(8)) AS DATE)
    FROM @tbl;
    0 comments No comments

  3. Bruce ( 44,621 Reputation points

    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()))
    0 comments No comments

  4. CosmogHong-MSFT 15,206 Reputation points Microsoft Vendor

    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.

    0 comments No comments