David Gelula David Gelula - 4 months ago 14
SQL Question

Convert decimal to date in order to use DATENAME function

Working with an ERP database (SQL Server) that stores dates as decimal columns in

YYYYMMDD
format - e.g.
20160801
. I need to convert these values into dates so that I can use the
DATENAME
function to identify the day of the week corresponding to each date.

Or, if there is another way to derive the day of the week, I am all ears. The rest of the query is simple ... I just need to return an Order Number, the Order Date (the column stored as a decimal), and the day of the week corresponding to the Order Date.

EDIT (taken from a comment)



I am still missing something as I'm getting a could not be bound error:

Select CAST(OEORDD.EXPDATE AS VARCHAR(8))
DECLARE @dec DECIMAL(10,0)=20160801;
SELECT CAST(CAST(@dec AS VARCHAR(8)) AS DATE) , sum(OEORDD.EXTWEIGHT) as ExtWeight
FROM OEORDD
GROUP BY EXPDATE

Answer

There is the "unserparated" datetime format, which is casteable natively. For this you'd need an 8-letter-string with yyyymmdd.

So first convert your decimal number to a string:

CAST(YourDecimalValue AS VARCHAR(8))

Then cast this to a date:

DECLARE @dec DECIMAL(10,0)=20160801;
SELECT CAST(CAST(@dec AS VARCHAR(8)) AS DATE)

UPDATE (concerning your comment)

Might be you need something like this:

SELECT  CAST(CAST(OEORDD.EXPDATE AS VARCHAR(8)) AS DATE) AS YourConvertedDate
       ,SUM(OEORDD.EXTWEIGHT) AS ExtWeight 
FROM OEORDD 
GROUP BY CAST(CAST(OEORDD.EXPDATE AS VARCHAR(8)) AS DATE)
Comments