MS Query

[Simon Shaw]

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

 

[Ed Ferrero]

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

 

[Simon Shaw]

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

 

[Ed Ferrero]

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

 

[Simon Shaw]

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

Thanks

Simon