sql server 2008 - Casting a String in SQL Query -

i have query extracts numeric value of date programmed in database long time ago , displays 19930215.202300000. 3 different columns i've done far cast string date , time

existing query:

 select serialnum [serial number],ts_sitename site,(case m.scratched  when 0 'live'  when 1 'free'  end) status,  note comment, (case destroyed when 0 'no'  when 1 'yes' end) [destroyed],   substring(cast(effectivedate char), 1, 8) [effective date added],   substring(cast(effectivedate char), 10, 6) [effective time added],   substring(cast(scratcheddate char), 1, 8) [scratched date],   substring(cast(scratcheddate char), 10, 6) [scratched time],   substring(cast(changedpurgedate char), 1, 8) [purge date],   substring(cast(changedpurgedate char), 10, 6) [purge time],   (select fl_filename thefiles_tab mg_filenum = fl_filenum) [dataset],  (select hs_hostname thehosts_tab mg_hostnum = hs_hostnum) [host],   (select usercode [user] mg_usernum = userid) [usercode]  ((media m left join mediagent g on m.mediaid = g.mg_medianum)   join thesites_tab s on m.siteid = s.ts_sitenum)   join note n on m.noteid = n.noteid; 

so displays effective date 20120327

however required output 2012-03-27

current display of effective time 213100

required output 21:31:00

.. , on other 4 date , time columns well.

please note i'm using sql server 2008 run queries.

can make edits code required output, i'm not great string maniupulation? love test out immediately.

maybe can use integrate code.

declare @d decimal(20,9) declare @yy varchar(4) declare @mm varchar(2) declare @dd varchar(2) declare @hh varchar(2) declare @mi varchar(2) declare @tt varchar(2) set @d = 19930215.202300000 set @yy = cast(left(@d,4) varchar) set @mm = cast(substring(cast(@d varchar),5,2) varchar) set @dd = cast(substring(cast(@d varchar),7,2) varchar) set @hh = cast(substring(cast(@d-floor(@d) varchar),3,2) varchar) set @mi = cast(substring(cast(@d-floor(@d) varchar),5,2) varchar) set @tt = case when cast(@hh int) > 12 'pm' else 'am' end select @d,@yy,@mm,@dd,@hh,@mi,@tt 

this outputs this:

19930215.202300000,1993,02,15,20,23,pm