Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
MS Query
I got it working, with the LEFT and RIGHT rather than SUBSTRING
Thanks Simon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |