casting date stored as integers

Hi
I have a SQL server database that have dates stored like 12142006003423 which means 2006-12-14 00:34:00
is there any way to cast it directly to a datetime type (I don't mean a user defined function as I've already implemented one)

thanks

Eisa:

Did you deliberately truncate the seconds out? And if so do you want the seconds truncated or rounded?

|||no,
it is just a COINCIDENCE|||

Eisa:

Here is a select statement to do this; however, it is a better option to have a front-end application do this formatting rather than have SQL Server perform this formatting:

declare @.dateTime bigint
declare @.dateTime2 bigint
set @.dateTime = 12142006003423
set @.dateTime2 = 8012006010203

select @.dateTime as [Date / Time],
convert (char(4), (@.dateTime/1000000)%10000) + '-' +
right ('0'+convert(varchar(2), (@.dateTime/cast(1000000000000 as bigint))%100), 2) + '-' +
right ('0'+convert(varchar(2), (@.dateTime/cast(10000000000 as bigint))%100), 2) + ' ' +
right ('0'+convert(varchar(2), (@.dateTime/10000)%100), 2) + ':' +
right ('0'+convert(varchar(2), (@.dateTime/100)%100), 2) + ':' +
right ('0'+convert(varchar(2), @.dateTime%100), 2)
as formattedDate

-- Output:

-- Date / Time formattedDate
-- -- -
-- 8012006010203 2006-08-01 01:02:03
-- 12142006003423 2006-12-14 00:34:23

|||Mugambo,
thanks for your interest. however I was looking for a direct cast or convert statement that can accomplish this task
I've created a scalar function for the ease of use as follows
CREATE FUNCTION Int2Date ( @.sdate bigint)
returns datetime as
begin
declare @.dt as varchar(14)
declare @.dd varchar(2)
declare @.mm varchar(2)
declare @.yy varchar(4)
declare @.hh varchar(2)
declare @.mi varchar(2)
declare @.ss varchar(2)
declare @.result varchar(25)

set @.dt = cast(@.sdate as varchar(14))
set @.mm = substring(@.dt,1,2)
set @.dd = substring(@.dt,3,2)
set @.yy = substring(@.dt,5,4)
set @.hh = substring(@.dt,9,2)
set @.mi = substring(@.dt,11,2)
set @.ss = substring(@.dt,13,2)

set @.result = cast (@.mm + '/' + @.dd + '/'+ @.yy + ' ' + @.hh + ':' + @.mi +':' + @.ss as datetime)
return @.result
end

In Oracle a to_date(8012006010203,'DDMMYYYYHHMISS') can do this in a single step i was wondering if SQL server have a similar function

thanks anyway