CONVERT Date INT to DATE in SQL

Question

How can I convert a date integer to a date type? (20200531 into 5/31/2020)

My current table has a datadate formatted as YYYYMMDD (20200531, 20200430, etc.)

The Datatype for the datadate is an int according the Toad Data Point software I'm using. I believe it's using ORACLE sql database.

As a result, when querying this data, I have to type in the where clause as below..

where datadate = '20200531'

My goal is to convert this integer datadate into a date format (5/31/2020) so I can apply the datadate to the where clause.

like..

WHERE datadate = dateadd(DD, -1, CAST(getdate() as date))

Answer 1

(Read below for my answer for if it's an int column)

Assuming it's a textual string:

Assuming that datadate is a string (character, text, etc) column and not a date/datetime/datetime2/datetimeoffset column, then use the CONVERT function with style: 23. The 23 value corresponds to ISO 8601 because the values are in yyyy-MM-dd-order, even though they're missing dashes.

This page has a reference of style numbers: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

SELECT
    *
FROM
    (
        SELECT
            myTable.*
            CONVERT( date, datadate, 23 ) AS valueAsDate
        FROM
            myTable
    ) AS q
WHERE
    q.valueAsDate = DATEADD( dd, -1, GETDATE() )

Assuming it's an actual int column:

The quick-and-dirty way is to convert the int to varchar and then use the same code as above as if it were a textual field - but don't do this because it's slow:

SELECT
    *
FROM
    (
        SELECT
            myTable.*,
            CONVERT( char(8), datadate ) AS valueAsChar,
            CONVERT( date, CONVERT( char(8), datadate ), 23 ) AS valueAsDate
        FROM
            myTable
    ) AS q
WHERE
    q.valueAsDate = DATEADD( dd, -1, GETDATE() )

Assuming it's an actual int column (better answer):

(I extend my gratitude to @alain for pointing out a rather egregious error in my arithmetic and providing a better solution)

We'll need to use DATEFROMPARTS and extract each component using Base-10 arithmetic (fun)!

Supposing we have an integer representing a formatted date _{(the horror)} such as 20200531 then we need to use integer-only arithmetic, such that division truncates rather than rounds - so dividing by 10000, we get 2020, then multiply back to get 20200000:

20200531 /    10000 =>      2020
    2020 *    10000 =>  20200000
20200531 - 20200000 =>       531
     531 /      100 =>         5
       5 *      100 =>       500
     531 -      500 =>        31

Or:

datadate /    10000 =>      yyyy
    yyyy *    10000 =>  yyyy0000
datadate - yyyy0000 =>      MMdd
    MMdd /      100 =>        MM
      MM *      100 =>      MM00
    MMdd -     MM00 =>        dd

...which reduces down to:

yyyy = ( datadate / 10000 )
  MM = ( datadate - ( yyyy * 10000 ) ) / 100
  dd = ( ( datadate - ( yyyy * 10000 ) ) - ( ( MM ) * 100 ) )

...which can be converted into a T-SQL scalar UDF:

CREATE FUNCTION dbo.UnhorriblifyDate( @date int )
RETURNS date
    WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT, INLINE = ON
AS
BEGIN

    DECLARE @yyyy int = @date / 10000;
    DECLARE @mm   int = ( @date - ( @yyyy * 10000 ) ) / 100;
    DECLARE @dd   int = ( ( @date - ( @yyyy * 10000 ) ) - ( ( @mm ) * 100 ) );
    
    DECLARE @result date = DATEFROMPARTS( @yyyy, @mm, @dd );
    RETURN @result;
END

Or in a query:

WITH dateParts AS (

SELECT
    c.datadata,
    c.yyyy,
    c.mm,
    ( ( c.datadate - ( c.yyyy * 10000 ) ) - ( ( c.MM ) * 100 ) ) AS dd
FROM
    (
        SELECT
            b.datadata,
            b.yyyy,
            ( b.datadate - ( b.yyyy * 10000 ) ) / 100 AS mm
        FROM
            (
                SELECT
                    a.datadata,
                    a.datadate / 10000 AS yyyy
                FROM
                    myTable AS a
            ) AS b
    ) AS c
)
SELECT
    c.datadata,
    DATEFROMPARTS( d.yyyy, d.mm, d.dd ) AS valueAsDate
FROM
    dateParts AS d;