Convert DATETIME to 8 digit INT in format YYYYMMDD SQL

Question

Need to join two tables. One table has a 'datekey' column that is an int in the format YYYYMMDD from the dimdate table, and the other table has a 'CreatedOn' column that is a datetime. I am casting the datetime to a date initially because that is the only way the group by seems to work correctly. I keep getting the error "Operand type clash: date is incompatible with int" because of the "on d.datekey = BBB.datekey" line.. any suggestions on an easy workaround?

select 
    distinct CONVERT (date,convert(char(8),d.datekey )) as Date
    , isnull(BBB.reccount,0) DimAccount
from dimdate d
left outer join(
    Select 
        cast(createdon as date) datekey
        , count(*) RecCount
    from DimAccount 
    group by cast(createdon as date)
)BBB on d.datekey = BBB.datekey
where d.datekey like '2017%'
group by d.datekey
,BBB.RecCount
having MAX(DATEDIFF(dd, CONVERT(date, CAST(d.Datekey  AS CHAR(8)), 101), GETDATE())) > -1
order by date desc

dev.mysql.com/doc/refman/5.7/en/… google is your friend (and docs too) — LordNeo
– LordNeo, Commented Jul 25, 2017 at 19:31
@AlekhyaVemavarapu that still returns 0 for all dates in the query, I'm not sure why — JD96
– JD96, Commented Jul 25, 2017 at 19:35
@DanBracuk sql server, in visual studio if that is what you mean — JD96
– JD96, Commented Jul 25, 2017 at 19:37

Dan Bracuk · Accepted Answer · 2017-07-25 19:47:47Z

2

something like this should get you started

from dimDate dd join dimAccount da 
on cast(da.createdOn as date) = cast(cast(dd.dateKey as char(8)) as date)

where dd.year = 2017  -- or you can filter on da.createdOn >= '20170101'

In fact, if your dimDate table has an actual date column you wouldn't have to cast the dateKey at all.

answered Jul 25, 2017 at 19:47

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert DATETIME to 8 digit INT in format YYYYMMDD SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related