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.
| Author |
Topic |
|
sebyinnz
Starting Member
2 Posts |
 Posted - 2004-09-30 : 00:39:32
|
| Hey folks,I'm trying to execute this query:SELECT Transaction.RegistrationID, Ceiling(DateDiff("n",[Transaction].[DateTime IN],[Transaction].[Date Time OUT])/15) AS [Whole Quarters]FROM [Transaction]WHERE (((Transaction.[DateTime IN]) Like "*/09/2004 *"));I get the following error: "Undefinied Function 'Ceiling' in Expression."Does anyone know what is wrong? The MS-Access help topic "Transact SQL scalar functions for expressions (ADP)" lists several mathematical functions of which Ceiling and Floor both don't work.If anyone has anyideas, I would much apreciate it. Thanks, Seby. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-09-30 : 09:05:51
|
| Why would you need to use CEILING at all when you are returning an int anyway?Also Ceiling doesn't work in access unless this is a pass-though to SQL Server... |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-30 : 09:12:29
|
quote:
WHERE (((Transaction.[DateTime IN]) Like "*/09/2004 *"))
What are you trying to do here? i would never use LIKE with datetime datatypes. if you want all transactions in September 2004, then say:WHERE Transaction.[DateTime IN] between #1/9/2004# and #30/9/2004#(I'm assuming your datetime format is d/m/y ).- Jeff |
|
|
|
sebyinnz
Starting Member
2 Posts |
Posted - 2004-10-06 : 07:54:51
|
| @RickD:I devide the integer returned by datediff by 15 with will mostly give me a decimal number back. I want to then round that number up. So 0.3 becomes 1.0 and not 0.0 .@jsmith8858:The where clause with "LIKE" works, so I'm not to worried about that.@all:The idea that I have come up with so far, is to take the returned integer of datediff and "mod" (modulo) it with 15. If the result is greater than 0, I add one to the datediff returned number. Someone told me I could use the IIf function in SQL (this would essentialy simulate the CEILING function), but if anyone has a better idea...It's quite tricky to get this right I think, I and would much appreciate any input. :)Seby. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-06 : 08:56:19
|
well you can always do select cast(DateDiff("n",[Transaction].[DateTime IN],[Transaction].[Date Time OUT])/15 as int) + 1 as [Whole Quarters]from ...that doesn't need a ceiling...isn't modulo operator % or mod in access and / for divide??Go with the flow & have fun! Else fight the flow  |
|
|
|
|
|
|
|
|
CEILING function help needed