Query won't work in SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Query won't work in SQL Server

Author

Topic

projecttoday
Starting Member

31 Posts

Posted - 2010-03-26 : 02:01:22

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 query?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-26 : 12:38:28

"The above works in Access. But the same code in SQL Server gives me a value of -41 where it should be 7."

Based on what INPUT values?