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 |
|
mapidea
Posting Yak Master
124 Posts |
 Posted - 2009-02-03 : 01:27:44
|
| select cast(shipdate - orderdate as bigint) as cdif,datediff(day, orderdate, shipdate) as diffrom ordersThe cast is throwing a "Arithmetic overflow error converting expression to data type datetime."The shipdate is 23-Dec-2004 12:00:00 AMThe orderdate is 05-Apr-3009 12:00:00 AMThe shipdate and orderdate are taken just for testing purpose.Why is the exception thrown? |
|
|
mapidea
Posting Yak Master
124 Posts |
Posted - 2009-02-03 : 01:45:15
|
| So that I will get the same results for cdif and dif |
 |
|
|
AvanthaSiriwardana
Yak Posting Veteran
78 Posts |
Posted - 2009-02-03 : 01:50:42
|
quote:
Originally posted by mapidea
So that I will get the same results for cdif and dif
what is yourdata type for shipdate and orderdateAvantha SiriwardanaBeware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth) |
|
|
|
mapidea
Posting Yak Master
124 Posts |
Posted - 2009-02-03 : 01:57:29
|
| datetime |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-03 : 01:58:48
|
| use datediff for difference between two datecolumnstry like thisdeclare @shipdate datetime, @orderdate datetimeselect @shipdate='23-Dec-2004 12:00:00 AM',@orderdate ='05-Apr-3009 12:00:00 AM'select datediff(day,@shipdate, @orderdate )select datediff(d,0,@orderdate) -datediff(d,0,@shipdate)declare @shipdate varchar(64), @orderdate varchar(64)select @shipdate='23-Dec-2004 12:00:00 AM',@orderdate ='05-Apr-3009 12:00:00 AM'select datediff(d,0,convert(datetime,@orderdate)) - datediff(d,0,convert(datetime,@shipdate)) |
|
|
|
mapidea
Posting Yak Master
124 Posts |
Posted - 2009-02-03 : 02:06:40
|
| Thanks. It worked. |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-03 : 02:09:32
|
welcome |
|
|
|
|
|
|
|
|
Cast