MS Query
 

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

MS Query
 

Hi Simon ,

MS Query is a very old app - I doubt it understands CAST or CONVERT.

If you know that the dates in SQL Server are always 8 charachters long, try
something like;

SELECT
LEFT(APPJH.DATEINVC,4) AS 'Year',
RIGHT(LEFT(APPJH.DATEINVC,6),2) AS 'Month',
RIGHT(APPJH.DATEINVC,2) AS 'Day', ...

Ed Ferrero
www.edferrero.com

MS Query
 

unfortunately the date fields are set as data type Decimal, so it won't let
me pull it apart with substing or the others...


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

MS Query
 

Hi Simon,

unfortunately the date fields are set as data type Decimal, so it won't
let
me pull it apart with substing or the others...

Hmnn...

What version of SQL Server and Excel are you using?

I built a small table in SQL Server 2005, with a Decimal data type field
containing dates in your format. Then I used Excel 2003 to run MS Query and
parsed the field as shown. Worked well for me.

Ed Ferrero
www.edferrero.com

MS Query
 

I got it working, with the LEFT and RIGHT rather than SUBSTRING

Thanks

Simon