this value was suppose to be datetime but by mistake the column datatype was given integer so now i want to convert it back to datetime
um, i don't think so.
if it was a direct convert to integer from SQL Server, the number would be 38959 as far as SQl server is concerned.
pretty sure my first guess is correct; it resolves to
'2006-09-01 00:00:00.000', so i'm pretty sure it was imported form another system as is, and was never a datetime in SQL that was accidentally stored in an integer column.
select CONVERT(decimal,convert(datetime,'2006-09-01 00:00:00.000'))
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!