October 1, 2007 at 5:05 am
Hello all,
If some one can explain this behavior it would be much appreciated. Attached is the T-SQL to reproduce the issue I am having. The following will throw the exception "Arithmetic overflow error converting expression to data type datetime".
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TheTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TheTable]
GO
CREATE TABLE [dbo].[TheTable] (
[ID] [int] NOT NULL ,
[TheDate] [datetime] NOT NULL ,
[TheOrder] [int] NOT NULL
) ON [PRIMARY]
GO
set nocount on
declare
@order int
insertinto TheTable ([ID], TheDate, TheOrder)
values(4, '2007-10-01', 2)
insertinto TheTable ([ID], TheDate, TheOrder)
values(4, '2007-10-03', 6)
select *
from TheTable
orderby TheOrder
set @order = 1
select *
from TheTable
orderby case @order
when 1 then TheDate
else TheOrder
end desc
set @order = 2
select *
from TheTable
orderby case @order
when 1 then TheDate
else TheOrder
end desc
insertinto TheTable ([ID], TheDate, TheOrder)
values(4, '2007-10-05', 40520631)
set @order = 2
select *
from TheTable
orderby case @order
when 1 then TheDate
else TheOrder
end desc
October 1, 2007 at 6:06 am
This isn't an issue with ORDER BY at all, it's a case of CASE 🙂
Here's what is happening...
This works: select cast(2 as datetime)
This doesn't work: select cast(40520631 as datetime)
..recognize the numbers from 'theorder' column?
Ok, why is this then?
It's how CASE works.
The CASE statement can only return *one* datatype.
This means that if your CASE refers to more than one datatype, and you don't do any explicit conversions yourself, there will be implicit conversions done for you instead.
As long as this works, all is fine, though it's shaky at best to rely on implicit conversions with CASE.
(as you've already found out)
In this example:
select *
from TheTable
order by case @order
when 1
then TheDate
else TheOrder
end desc
..the CASE refers to a datetime and an int and there are no explicit converts, so there will be implicit conversions done automagically. The question is what is converted to what?
That's decided by the datatype precedence.
Datetime has precedence over int, so that is what this CASE will return
(and also convert everything in it to, in order to resolve)
/Kenneth
October 1, 2007 at 8:34 am
October 1, 2007 at 8:42 am
Thanks Kenneth, makes sense now.
October 1, 2007 at 8:50 am
bledu (10/1/2007)
why?? I have never seen a case... being used in an Order by.if you want to do that stuff the way you want i think you are better off using dynamic sql to build your query string
Oh, it's perfectly ok to use a case in an order by. The trick is only to 'get it right'.
And NO!!!! Absolutely NO reason to resort to dynamic SQL to do it!
/Kenneth
October 1, 2007 at 10:51 am
October 1, 2007 at 2:54 pm
You can simply show by which column(s) to order:
ORDER BY 1
- orders by 1st column in returning recordset;
ORDER BY 2, 3
- orders by 2nd, them 3rd column in recordset
ORDER BY 3, 1, '1'
- orders by 3rd and 1st column, '1' is a string constant, is ignored.
A lot of cool options here.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply