asked
convert to datetime
When I try converting a varchar column to datetime, it gets done, but a dynamic conversion gives an error. **select filingId, filingToPeriodId, Blockgroupid, CONVERT(varchar, CONVERT(decimal(15, 0), dataItemValue)) decimalPEOValue into processing_AsReportedPEO_tbl from Processing_AsReportedData_tbl** **Select top 10 * from processing_AsReportedPEO_tbl where isdate(CONVERT(datetime, decimalPEOValue))** This works perfectly well. Both the following give errors. I can't understand why. **1. Select top 10 * from Processing_AsReportedData_tbl where isdate(CONVERT(datetime, CONVERT(DECIMAL(15,0), dataItemValue), 113))=0** **2. Select top 10 * from Processing_AsReportedData_tbl where isdate(CONVERT(datetime, CONVERT(varchar, CONVERT(decimal(15, 0), dataItemValue))))=0** Please help me.
It seems likely that the column `dataItemValue` contains characters that represent decimal values that are either too large or too small to be converted to datetime The largest decimal that can be converted to datetime in this manner is 2958463.49... and the smallest is -53690.49... If you have any values that fall outside of that range, you will get the `Arithmetic overflow error...` Here you can see the conversion reaching the upper and lower limits of the datetime data type: declare @dateString varchar(30); set @dateString = '2958463.49'; select CONVERT(datetime, convert(decimal(15, 0), @dateString), 113); set @dateString = '-53690.4999999'; select CONVERT(datetime, convert(decimal(15, 0), @dateString), 113); The following query should show if you have any values outside of the datetime range: SELECT COUNT(*) FROM Processing_AsReportedData_tbl WHERE CONVERT(decimal(15, 0), dataItemValue) > 2958463.49 OR CONVERT(decimal(15, 0), dataItemValue) < -53690.49
13 People are following this question.
