SQL Dialect Problem

[oliverpenney]

hi all

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

and hurrah it works

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

 

[Crowley16]

convert doesn't work in access...

if the date is already in date datatype, then use format(date, "dd mm yy; hh:mm:ss") to format
if the date isn't in date datatype, then use cDate to convert it to date...

 

[PHV]

In the Select list, replace this:
Convert(smalldatetime,convert(char(8),si.TransactionDateTime,112)) as Tday,
By this:
si.TransactionDateTime as Tday,
And in the where clause you may try this:
si.TransactionDateTime >= '2004-06-24'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

This is using style 112, which apparently strips offs the time portion so that this results in the date without the time. Is that correct?

Convert(smalldatetime,convert(char(8),si.TransactionDateTime,112)) as Tday

The format function could be used to give the date without the time.
Format(si.TransactionDateTime,"Short Date") as Tday

Something similar may work in the min and max.
Max(si.TransactionDateTime - Format(si.TransactionDateTime,"Short Date") ) as Maxtime

OR Access has additional date functions such as:
FormatDateTime
DateSerial
TimeSerial