SQL Dialect Problems

[oliver_penney]

hi all

i have some SQL that i run in a spreadsheet that goes to SQL Server and gets a load of stuff

and it works, hurrah

but i want it to go into Access 2000. so i ODBC all the tables into an .mdb and go to the SQL design of a query, naively paste in the SQL and it doesn't work...

i figure out that you need INNER JOIN instead of JOIN, but i'm having hell with date formats. all i wanna do is pull a date/time field out in the WHERE bit, if SQL server wants me to use CONVERT and Access doesn't know what CONVERT is!



any hoo

here is the code that works from the spready

SELECT

si.UnitId,
f.UnitDescription,
Convert(smalldatetime,convert(char(8),si.TransactionDateTime,112)) as Tday,
f.Level2Desc,
f.Level3Desc,
Min(si.TransactionDateTime - Convert(smalldatetime,convert(char(8),si.TransactionDateTime,112))) as Mintime,
Max(si.TransactionDateTime - Convert(smalldatetime,convert(char(8),si.TransactionDateTime,112))) as Maxtime

FROM
SaleItem2 si
JOIN
v_FinancialDimension_For_Cube f
ON si.UnitId = f.UnitId

WHERE
f.Level1Desc= 'Retail - Rail' AND
si.TransactionDateTime >= '20040624' AND
si.TransactionDateTime <= '20040701'

GROUP BY
si.UnitId,
f.UnitDescription,
f.Level2Desc,
f.Level3Desc,
Convert(smalldatetime,convert(char(8),si.TransactionDateTime,112))

any ideas?

thanx in advance

ol

 

[Norie]

Why use the SQL?

Why not try using the Query Designer to recreate your query?

Or perhaps you could import the query into Access and it might convert it for you?

 

[mdmilner]

Most likely, it's looking at these lines as comparing text to date.

si.TransactionDateTime >= '20040624' AND 
si.TransactionDateTime <= '20040701'

Try:

si.TransactionDateTime >= '#20040624#' AND 
si.TransactionDateTime <= '#20040701#'

Mike