Get date from int (YYYYMMDD)

Question

By doing a mistake a few months ago I got myself in a situation, where I have to find a workaround for the following problem:

I am saving dates as integer in a common format inside my SQL Server database (YYYYMMDD). I want to have my select statement giving me the integer in a format, that looks like a date (or even is in a true date type) to the user, so I can use the received datatable directly as datasource for my DataGridView.

• I do not want to use clientside formatting
• Let's call the table myTable and the integer/date column myIntDate
• myIntDate can be NULL
• sample myIntDate-value : 20160803 for the 3rd of August 2016

Solution

Select cast(cast(20160729 as varchar(10)) as date)

Returns

2016-07-29

Or

 Select cast(left(20160729,8) as date)