Cast varchar as date select distinct top 100

Question

I am trying to fix a query that has come to light in SSRS after the new year. We have an input that comes from another application. It grabs a date and stores it as varchar. The SSRS report then fetches the top 100 'dates' but when 2017 dates have come around, this are not in the top 100.

The existing query is as follows

SELECT DISTINCT TOP (100)
FROM DenverTempData
ORDER by BY Date DESC

The date is stored as VARCHAR. So obviously this query doesn't grab a value such as 01012017 as being a top 100 (over values likes 12312016). I thought maybe I can simply change the datatype on this column to datetime. But the information comes from a flat file and is converted, so it's a little more difficult that that. So I'm hoping to do a select of the distinct top 100 while converting the date column to datetime or just date and grabbing the last 100 dates.

Can someone help with the query syntax? I'm thinking a cast to convert varchar to date, but how do I format with distinct top 100? I'm simply looking to retrieve the last 100 dates in chronological order from a column that is stored as varchar but contains a string representing a date.

Hopefully that makes sense

Answer 1

It is always a bad idea to store a date as string. This is highly culture specific!

You can cast your formatted string-date to a real date like this:

DECLARE @DateMMDDYYYY VARCHAR(100)='12312016';

SELECT CONVERT(DATE,STUFF(STUFF(@DateMMDDYYYY,5,0,'-'),3,0,'-'),110)

After the conversion your sorting (and therefore the TOP 100) should work as expected.

My strong advise: Try to store your dates in a column of a real date type to avoid such hassel!

Answer 2

SELECT  DISTINCT TOP 100 (CAST(VarcharColumn as Date) as DateColumn)
FROM TABLE
Order by DateColumn desc