[펌] string을 datetime 으로 변환

-- CHARINDEX,CONVERT,REPLACE 를이용데이터입력

insert into test (a,b,c)

values (1,'홍길동' ,

case when  CHARINDEX('오후','2008-07-01 오전11:29:34')>0

       then dateadd(hh,12,convert (datetime, replace('2008-07-01 오전11:29:34','오후','')))

       else convert (datetime,replace('2008-07-01 오전11:29:34','오전','') )

       end

)

insert into test (a,b,c)

values (1,'변학도' ,

case when  CHARINDEX('오후','2008-07-01 오후02:11:34')>0

       then dateadd(hh,12,convert (datetime, replace('2008-07-01 오후02:11:34','오후','')))

       else convert (datetime,replace('2008-07-01 오후02:11:34','오전','') )

       end

)

-- 날짜변환하는부분을함수로작성

CREATE  FUNCTION dbo.fn_Changedatetime (

       @sdate varchar(30)

       )RETURNS DATETIME

AS

BEGIN

       DECLARE @RETURNVALUE DATETIME

        BEGIN

               SELECT  @RETURNVALUE=CASE WHEN  CHARINDEX('오후',@sdate)>0

                    THEN DATEADD(HH,12,CONVERT (DATETIME, REPLACE(@sdate,'오후','')))

                    ELSE CONVERT (DATETIME,REPLACE(@sdate,'오전','') )

                    END

        END

        RETURN(@RETURNVALUE)

END

----------------------------------------------------------------------------- 

-- 사용자함수를이용하여간편하게데이터입력

insert into test (a,b,c)

values (3,'김일성' , dbo.fn_Changedatetime('2008-06-27 오후08:21:14'))

-- 입력된값확인

select * from test

[출처] MSSQL string을 datetime 으로 변환|작성자 보글옵하