Q: How to Convert Decimal to DateTime in where clause I'm trying to Convert a Decimal DateTime value to DateTime datatype and check a condition in a select query
SELECT * FROM CLBALTRNTBL WHERE CONVERT(DATETIME,LSTDAT) >= @sDt 
      AND CONVERT(DATETIME,LSTDAT) <= @eDt

but the following error Occurs
Arithmetic overflow error converting expression to data type datetime.

I'm doing this in a stored Procedure. and the @sDtand @eDt are DateTime Variables. The LSTDAT is in the format yyyyMMddie 20120317
But if I enter the LSTDAT column directly ,for ex:20130805, the query executes. but what with the column??
Can you help me out ?

A: Try this : 
SELECT *
FROM CLBALTRNTBL
WHERE CONVERT(DATETIME, CONVERT(VARCHAR(8), LSTDAT), 112) >= @sDt
    AND CONVERT(DATETIME, CONVERT(VARCHAR(8), LSTDAT), 112) <= @eDt


A: It's way more efficient to convert your variables and compare to column - than to convert your column.
SELECT * 
FROM CLBALTRNTBL 
WHERE LSTDAT >= CONVERT(INT,CONVERT(NVARCHAR(8),@sDt,112))
  AND LSTDAT <= CONVERT(INT,CONVERT(NVARCHAR(8),@eDt,112))

SQLFiddle DEMO

A: SELECT * FROM CLBALTRNTBL WHERE LSTDAT >= convert(char(8),@sDt, 112) 
AND CONVERT(DATETIME,LSTDAT) <= convert(char(8),@eDt, 112)

This will prevent your errors and perform faster, however your do have some invalid LSTDAT in the base that cannot be converted to dates

A: SELECT * FROM CLBALTRNTBL WHERE CONVERT(DATETIME,LSTDAT) >= @sDt 
      AND CONVERT(DATETIME,Convert(VarChar(8),LSTDAT),112) <= @eDt

would be one way.
e.g. 20120317 to '20120317' then 112 is ISO format yyyyMMdd
