October 14, 2010 at 5:41 am
I'm getting data from an external source that comes in with dates in decimal format.
Sample data:
CREATE TABLE #DatesHeldAsDecimal
(DecimalDate DECIMAL(8,0) NULL)
-- Insert test data
INSERT INTO #DatesHeldAsDecimal
(DecimalDate )
VALUES
(20080905)
When I try to convert the decimal data directly to datetime
SELECT CAST(DecimalDate AS DATETIME) FROM #DatesHeldAsDecimal
I get this error:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
However if I convert to nvarchar (or varchar) first, the conversion works:
SELECT
CAST(CAST(DecimalDate AS nvarchar(8))AS DATETIME)
FROM #DatesHeldAsDecimal
The result is:
2008-09-05 00:00:00.000
There are a large number of rows in the table and I have to convert each time an update comes in which takes a long time each week. I suspect converting to datetime directly rather than to nvarchar first would be faster if it were possible. Solutions I've read on different forums recommend to convert to nvarchar first but don't explicitly say that direct conversion is impossible. Is it impossible?
October 14, 2010 at 5:51 am
Hmm upon further testing I see why its not possible to convert decimal dates with the current year in yyyymmdd format to datetime. Numbers are interpreted as 'how many days from 1st January 1901' is this number? so for example
SELECT CAST(1 AS DateTime)
gives result 1900-01-02 00:00:00.000
Therefore Casting 20100814 would yield a result 20,100,814 days after 1st Jan 1901 (sometime in the year 56,971 A.D. !!!). This is out of the range of dates allowed by datetime which only allows dates up to December 31, 9999.
October 15, 2010 at 4:32 am
I find it unlikely that the cast to nvarchar is causing your performance problems. If you are updating a large number of rows, most of the time is probably spent reading and writing to disk.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply