I am trying to connect an Excel PivotTable to an MS SQL database using MS
Query. The database fields that contain dates are setup as data type decimal
using the format 'YYYYMMDD". I need to convert it to a string so I can then
use substring to pull out the year month and day.

In MS Query I have tried using the SQL code:

SELECT SUBSTRING(CAST(APPJH.DATEINVC As Char(8)),1,4),
SUBSTRING(CAST(APPJH.DATEINVC As Char(8)),5,2), APPJH.DATEINVC,
SUBSTRING(CAST(APPJH.DATEDUE As Char(8)),1,4), SUBSTRING(CAST(APPJH.DATEDUE
As Char(8)),5,2), APPJH.DATEDUE, SUBSTRING(CAST(APPJH.DATEDISC As
Char(8)),1,4), SUBSTRING(CAST(APPJH.DATEDISC As Char(8)),5,2),
APPJH.DATEDISC, APPJD.AMTEXTNDHC, APPJD.BASETAXHC, APPJD.AMTDSCHCUR
FROM SAMLTD.dbo.APPJH APPJH

I have also tried using the CONVERT syntax in place of CAST


Thoughts?

Thanks

--
Simon Shaw, CA | President | Kode101 Inc.
www.kode101.com