Programing

oracle to mssql tip

handam 2013. 10. 11. 16:15

---to_char()
REPLACE(REPLACE(REPLACE(CONVERT(varchar(20), getdate(), 120),' ',''),':',''),'-','')


---rownum
CEILING(ROW_NUMBER() OVER(ORDER BY  A.STATE, A.REQ_DT DESC, F.CUST_NAME)/?) AS PAGE, 
ROW_NUMBER() OVER(ORDER BY  A.STATE, A.REQ_DT DESC, F.CUST_NAME) AS RNUM 
FROM (

 

---DECODE
CASE  a.CUSTOMER_ID WHEN 'anyone' THEN ISNULL(a.CUSTOMER_NAME,'비회원')
ELSE  (SELECT USER_NAME FROM TB_USER WHERE USER_ID=a.CUSTOMER_ID)
END AS  CUSTOMER_NAME,

 

---날짜계산

datediff 함수

DATEDIFF([년, 월, 일, 시, 분, 초, 밀리초], 시작날짜, 끝나는날짜)

년 = yy

월 = mm

일 = dd

시 = hh

분 = mi

초 = ss

 

ex) substr(to_char(DECODE(NVL(a.FAIL_END_DT,''),'',systimestamp, to_timestamp(a.FAIL_END_DT, 'yyyymmddhh24miss')) -
      to_timestamp(a.FAIL_START_DT, 'yyyymmddhh24miss')),9,8) as FAIL_TIME,

 

mssql 에서는 datediff 함수를 사용하면 int 형태로 되돌려준다.

 

하지만 '20121005182611' 을 varchar 형태로 convert 하면 error 가 난다.

년월일 형태는 정상적으로 select 되지만 시분초가 붙어 있으면 지랄같다.

 

방법은 아래와 같다.

(참조 : http://www.cyworld.com/tzlerus/8932639)

 

SELECT (CONVERT(DATETIME,
(
SUBSTRING( '20121005182611', 1, 4 ) + '-' +
SUBSTRING( '20121005182611', 5, 2 ) + '-' +
SUBSTRING( '20121005182611', 7, 2 ) + ' ' +
SUBSTRING( '20121005182611', 9, 2 ) + ':' +
SUBSTRING( '20121005182611', 11, 2 ) + ':' +
SUBSTRING( '20121005182611', 13, 2 )
)
)
)

 

변환하는 법을 찾기는 했지만 기존의 오라클에서 select 되는 값은 '00 00:00' 형태였다.... 일 시:분

mssql 에서 구현하기 위해 하드하게 헤딩 시작.

 

'일 시:분' 을 return 받았지만 문제는 한 자리 수일 때 '01 09:58' 형태가 아닌 '1 9:58' 형태이다... -_-

 

좀 더 하드하게 헤딩, 아래와 같이 구현하였다.

 

len 함수를 사용하여 자리수를 비교하려고도 했지만 큰 착오였다..

0 도 한 자리고 1~9 도 한 자리였기 때문에 순수하게 return 받은 int  값을 비교했다.

 

select

--SELECT

case  when DATEDIFF(dd, CONVERT(DATETIME, SUBSTRING('20070817000000', 1, 4) + '-' +
            SUBSTRING('20070817000000', 5, 2) + '-' +
            SUBSTRING('20070817000000', 7, 2) + ' ' +
            SUBSTRING('20070817000000', 9, 2) + ':' +
            SUBSTRING('20070817000000', 11, 2) + ':' +
            SUBSTRING('20070817000000', 13, 2)),
      CASE ISNULL('20070820000000','') WHEN '' THEN GETDATE() 
      ELSE CONVERT(DATETIME, SUBSTRING('20070820000000', 1, 4) + '-' +
               SUBSTRING('20070820000000', 5, 2) + '-' +
               SUBSTRING('20070820000000', 7, 2) + ' ' +
               SUBSTRING('20070820000000', 9, 2) + ':' +
               SUBSTRING('20070820000000', 11, 2) + ':' +
               SUBSTRING('20070820000000', 13, 2))
      END
      ) < 10 -- 10보다 작은 한 자리수라면 
  then '0' + convert(varchar(2),DATEDIFF(dd, CONVERT(DATETIME, SUBSTRING('20070817000000', 1, 4) + '-' +
            SUBSTRING('20070817000000', 5, 2) + '-' +
            SUBSTRING('20070817000000', 7, 2) + ' ' +
            SUBSTRING('20070817000000', 9, 2) + ':' +
            SUBSTRING('20070817000000', 11, 2) + ':' +
            SUBSTRING('20070817000000', 13, 2)),
      CASE ISNULL('20070820000000','') WHEN '' THEN GETDATE() 
      ELSE CONVERT(DATETIME, SUBSTRING('20070820000000', 1, 4) + '-' +
               SUBSTRING('20070820000000', 5, 2) + '-' +
               SUBSTRING('20070820000000', 7, 2) + ' ' +
               SUBSTRING('20070820000000', 9, 2) + ':' +
               SUBSTRING('20070820000000', 11, 2) + ':' +
               SUBSTRING('20070820000000', 13, 2))
      END
      )) -- '01' 로 select 
else convert(varchar(2),DATEDIFF(dd, CONVERT(DATETIME, SUBSTRING('20070817000000', 1, 4) + '-' +
            SUBSTRING('20070817000000', 5, 2) + '-' +
            SUBSTRING('20070817000000', 7, 2) + ' ' +
            SUBSTRING('20070817000000', 9, 2) + ':' +
            SUBSTRING('20070817000000', 11, 2) + ':' +
            SUBSTRING('20070817000000', 13, 2)),
      CASE ISNULL('20070820000000','') WHEN '' THEN GETDATE() 
      ELSE CONVERT(DATETIME, SUBSTRING('20070820000000', 1, 4) + '-' +
               SUBSTRING('20070820000000', 5, 2) + '-' +
               SUBSTRING('20070820000000', 7, 2) + ' ' +
               SUBSTRING('20070820000000', 9, 2) + ':' +
               SUBSTRING('20070820000000', 11, 2) + ':' +
               SUBSTRING('20070820000000', 13, 2))
      END
      )
) end +' '+ 
      
--SELECT
case when (DATEDIFF(hh, CONVERT(DATETIME, SUBSTRING('20070817000000', 1, 4) + '-' +
            SUBSTRING('20070817000000', 5, 2) + '-' +
            SUBSTRING('20070817000000', 7, 2) + ' ' +
            SUBSTRING('20070817000000', 9, 2) + ':' +
            SUBSTRING('20070817000000', 11, 2) + ':' +
            SUBSTRING('20070817000000', 13, 2)),
      CASE ISNULL('20070820000000','') WHEN '' THEN GETDATE() 
      ELSE CONVERT(DATETIME, SUBSTRING('20070820000000', 1, 4) + '-' +
               SUBSTRING('20070820000000', 5, 2) + '-' +
               SUBSTRING('20070820000000', 7, 2) + ' ' +
               SUBSTRING('20070820000000', 9, 2) + ':' +
               SUBSTRING('20070820000000', 11, 2) + ':' +
               SUBSTRING('20070820000000', 13, 2))
      END
      ) % 24) < 10 -- 일 수를 제외한 나머지 값(시간)이 10보다 작은 한 자리 수라면
  then '0'+CONVERT(varchar(2), (DATEDIFF(hh, CONVERT(DATETIME, SUBSTRING('20070817000000', 1, 4) + '-' +
            SUBSTRING('20070817000000', 5, 2) + '-' +
            SUBSTRING('20070817000000', 7, 2) + ' ' +
            SUBSTRING('20070817000000', 9, 2) + ':' +
            SUBSTRING('20070817000000', 11, 2) + ':' +
            SUBSTRING('20070817000000', 13, 2)),
      CASE ISNULL('20070820000000','') WHEN '' THEN GETDATE() 
      ELSE CONVERT(DATETIME, SUBSTRING('20070820000000', 1, 4) + '-' +
               SUBSTRING('20070820000000', 5, 2) + '-' +
               SUBSTRING('20070820000000', 7, 2) + ' ' +
               SUBSTRING('20070820000000', 9, 2) + ':' +
               SUBSTRING('20070820000000', 11, 2) + ':' +
               SUBSTRING('20070820000000', 13, 2))
      END
      ) % 24))
else CONVERT(varchar(2), (DATEDIFF(hh, CONVERT(DATETIME, SUBSTRING('20070817000000', 1, 4) + '-' +
            SUBSTRING('20070817000000', 5, 2) + '-' +
            SUBSTRING('20070817000000', 7, 2) + ' ' +
            SUBSTRING('20070817000000', 9, 2) + ':' +
            SUBSTRING('20070817000000', 11, 2) + ':' +
            SUBSTRING('20070817000000', 13, 2)),
      CASE ISNULL('20070820000000','') WHEN '' THEN GETDATE() 
      ELSE CONVERT(DATETIME, SUBSTRING('20070820000000', 1, 4) + '-' +
               SUBSTRING('20070820000000', 5, 2) + '-' +
               SUBSTRING('20070820000000', 7, 2) + ' ' +
               SUBSTRING('20070820000000', 9, 2) + ':' +
               SUBSTRING('20070820000000', 11, 2) + ':' +
               SUBSTRING('20070820000000', 13, 2))
      END
      ) % 24)
) end +':'+
      
--SELECT 
case when (DATEDIFF(mi, CONVERT(DATETIME, SUBSTRING('20070817000000', 1, 4) + '-' +
            SUBSTRING('20070817000000', 5, 2) + '-' +
            SUBSTRING('20070817000000', 7, 2) + ' ' +
            SUBSTRING('20070817000000', 9, 2) + ':' +
            SUBSTRING('20070817000000', 11, 2) + ':' +
            SUBSTRING('20070817000000', 13, 2)),
      CASE ISNULL('20070820000000','') WHEN '' THEN GETDATE() 
      ELSE CONVERT(DATETIME, SUBSTRING('20070820000000', 1, 4) + '-' +
               SUBSTRING('20070820000000', 5, 2) + '-' +
               SUBSTRING('20070820000000', 7, 2) + ' ' +
               SUBSTRING('20070820000000', 9, 2) + ':' +
               SUBSTRING('20070820000000', 11, 2) + ':' +
               SUBSTRING('20070820000000', 13, 2))
      END
      ) % 60) < 10 --시 수를 제외한 나머지 값(분)이 10보다 작은 한 자리수라면
  then '0'+convert(varchar(2), (DATEDIFF(mi, CONVERT(DATETIME, SUBSTRING('20070817000000', 1, 4) + '-' +
            SUBSTRING('20070817000000', 5, 2) + '-' +
            SUBSTRING('20070817000000', 7, 2) + ' ' +
            SUBSTRING('20070817000000', 9, 2) + ':' +
            SUBSTRING('20070817000000', 11, 2) + ':' +
            SUBSTRING('20070817000000', 13, 2)),
      CASE ISNULL('20070820000000','') WHEN '' THEN GETDATE() 
      ELSE CONVERT(DATETIME, SUBSTRING('20070820000000', 1, 4) + '-' +
               SUBSTRING('20070820000000', 5, 2) + '-' +
               SUBSTRING('20070820000000', 7, 2) + ' ' +
               SUBSTRING('20070820000000', 9, 2) + ':' +
               SUBSTRING('20070820000000', 11, 2) + ':' +
               SUBSTRING('20070820000000', 13, 2))
      END
      ) % 60))
else convert(varchar(2), DATEDIFF(mi, CONVERT(DATETIME, SUBSTRING('20070817000000', 1, 4) + '-' +
            SUBSTRING('20070817000000', 5, 2) + '-' +
            SUBSTRING('20070817000000', 7, 2) + ' ' +
            SUBSTRING('20070817000000', 9, 2) + ':' +
            SUBSTRING('20070817000000', 11, 2) + ':' +
            SUBSTRING('20070817000000', 13, 2)),
      CASE ISNULL('20070820000000','') WHEN '' THEN GETDATE() 
      ELSE CONVERT(DATETIME, SUBSTRING('20070820000000', 1, 4) + '-' +
               SUBSTRING('20070820000000', 5, 2) + '-' +
               SUBSTRING('20070820000000', 7, 2) + ' ' +
               SUBSTRING('20070820000000', 9, 2) + ':' +
               SUBSTRING('20070820000000', 11, 2) + ':' +
               SUBSTRING('20070820000000', 13, 2))
      END
      ) % 60)
end

 

음... 시간이 꽤 걸렸다. 머리가 나빠서...

 

좀 더 좋은 방법이 있다면 리플달아주세요 -0-;

반응형