R
Robert
I am trying to convert an Access backend database to SQL Server with an
ODBC connection and linked tables from the Access frontend to the SQL
Server backend. The SQL statements remain Jet SQL. I have a SELECT
statement with a Datediff function that works in Access but
not in SQL Server.
The following works in the existing Access:
SELECT DateDiff("n",startdateandtime+Nz(lunchtime,0),enddateandtime)/60)
It does not work with SQL Server. The problem is with lunchtime. I
tried removing lunchtime from the SQL Server version and that much
works:
SELECT DateDiff("n",startdateandtime,enddateandtime)/60)
but the lunchtime is not deducted from the total time. What I am doing
is computing total time worked, which is
enddateandtime - startdateandtime - lunchtime
enddateandtime and startdateandtime must contain a date and a time
because an employee can work past midnight. lunchtime is simply an
amount of time, not a time of day. So if a person took an hour and a
half for lunch it would be 1:30. The goal is the total worked time in
hours. The above works in Access. But the same code in SQL Server gives
me a value of -41 where it should be 7.
My workaround is to change the lunchtime to a numeric field but that
would involve changing every form and report that references it. Does
anybody know how I can deduct this lunchtime amount in my linked SQL
Server/Jet query?
ODBC connection and linked tables from the Access frontend to the SQL
Server backend. The SQL statements remain Jet SQL. I have a SELECT
statement with a Datediff function that works in Access but
not in SQL Server.
The following works in the existing Access:
SELECT DateDiff("n",startdateandtime+Nz(lunchtime,0),enddateandtime)/60)
It does not work with SQL Server. The problem is with lunchtime. I
tried removing lunchtime from the SQL Server version and that much
works:
SELECT DateDiff("n",startdateandtime,enddateandtime)/60)
but the lunchtime is not deducted from the total time. What I am doing
is computing total time worked, which is
enddateandtime - startdateandtime - lunchtime
enddateandtime and startdateandtime must contain a date and a time
because an employee can work past midnight. lunchtime is simply an
amount of time, not a time of day. So if a person took an hour and a
half for lunch it would be 1:30. The goal is the total worked time in
hours. The above works in Access. But the same code in SQL Server gives
me a value of -41 where it should be 7.
My workaround is to change the lunchtime to a numeric field but that
would involve changing every form and report that references it. Does
anybody know how I can deduct this lunchtime amount in my linked SQL
Server/Jet query?