농부지기

[ DB2.일반함수 - 비교.판단_함수 ]

1. CASE문
      : 정의 : 비교, 조건
      : 문장 :  CASE WHEN  조건문 THEN 결과
                            WHEN 조건문 THEN 결과
                   END

[ DB2.일반함수 - 문자_함수 ]

1. RTRIM
     : 정의 : 공백자르기, 오른쪽공백자르기
       RTRIM(문자컬럼)

2. LENGTH
    : 정의 : 길이, 싸이즈, SIZE

3. REPLACE
    : 정의 : 변환, 바꾸기

[ DB2.일반함수 - 문자_함수 ]

1. 특정일자에 년수 더하기
      1.1 예제 1
             : SELECT current date + 1 YEAR FROM sysibm.sysdummy1
                    -> YEAR를 사용하면 YEAR앞쪽의 숫자를 연도로 변환시킨다.

        1.2 예제 2
            : arg : asDate : 문자형날짜
                      aiYear  : 더하고 싶은 년수(숫자형)

            :  SUBSTR(REPLACE(CHAR(DATE(SUBSTR(asDate,1,4) ||'-'|| SUBSTR(asDate,5,2) ||'-'|| SUBSTR(asDate,7,2)) + aiYear YEAR ),'-',''),1,8)

            : 쪼개기 : 1. 날짜를 '-' formatting 한다                             : (SUBSTR(asDate,1,4) ||'-'|| SUBSTR(asDate,5,2) ||'-'|| SUBSTR(asDate,7,2))
                           2. 문자형날짜를  Date 형으로 변환                    : (DATE( 1번결과 )
                           3. Date형에서 년수를 더한후 문자형으로 변환   : CHAR(2번결과  + aiYear YEAR )
                           4. '-'가 있는 문자형날짜를 '-'를 제거                : REPLACE(3번결과,'-','')
                           5. 문자형날짜에서 앞쪽 년월일만 취함               : SUBSTR(4번결과,1,8)

2. 특정일자에 월수 더하기
    : arg : asDate : 문자형날짜
              aiMonth  : 더하고 싶은 월수(숫자형)

    :  SUBSTR(REPLACE(CHAR(DATE(SUBSTR(asDate,1,4) ||'-'|| SUBSTR(asDate,5,2) ||'-'|| SUBSTR(asDate,7,2)) + aiMonth MONTH ),'-',''),1,8)

    : 쪼개기 : 1. 날짜를 '-' formatting 한다                                : (SUBSTR(asDate,1,4) ||'-'|| SUBSTR(asDate,5,2) ||'-'|| SUBSTR(asDate,7,2))
                   2. 문자형날짜를  Date 형으로 변환                      : (DATE( 1번결과 )
                   3. Date형에서 월수를 더한후 문자형으로 변환     : CHAR(2번결과  + aiMonth MONTH )
                   4. '-'가 있는 문자형날짜를 '-'를 제거                  : REPLACE(3번결과,'-','')
                   5. 문자형날짜에서 앞쪽 년월일만 취함                 : SUBSTR(4번결과,1,8)

3. 특정일자에 일수더하기
     3.1 예제 1
            : SELECT current date + 3 YEARS + 2 MONTHS + 15 DAYS  FROM sysibm.sysdummy1
                     -> YEARS, MONTHS, DAYS를 사용하면 앞쪽 숫자를 년/월/일로 변환시킨다.

     3.2 예제 2
            : arg : asDate : 문자형날짜
                       aiDays  : 더하고 싶은 일자(숫자형)

            :  SUBSTR(REPLACE(CHAR(DATE(SUBSTR(asDate,1,4) ||'-'|| SUBSTR(asDate,5,2) ||'-'|| SUBSTR(asDate,7,2)) + aiDays DAYS ),'-',''),1,8)

            : 쪼개기 : 1. 날짜를 '-' formatting 한다                                : (SUBSTR(asDate,1,4) ||'-'|| SUBSTR(asDate,5,2) ||'-'|| SUBSTR(asDate,7,2))
                           2. 문자형날짜를  Date 형으로 변환                      : (DATE( 1번결과 )
                           3. Date형에서 일수를 더한후 문자형으로 변환      : CHAR(2번결과  + aiDays DAYS )
                           4. '-'가 있는 문자형날짜를 '-'를 제거                  : REPLACE(3번결과,'-','')
                           5. 문자형날짜에서 앞쪽 년월일만 취함                 : SUBSTR(4번결과,1,8)

4. 시간:분:초 더하기
     1. 예제 1
              : SELECT current time + 5 HOURS - 3 MINUTES + 10 SECONDS FROM sysibm.sysdummy1
                          -> HOURS, MINUTES, SECONDS를 사용하면 앞쪽 숫자를 시간/분/초로 변환시킨다.

5. 두 날짜 사이의 간격구하기
     1. 예제 1
              : SELECT days (current date) - days (date('1999-10-22'))  FROM sysibm.sysdummy1
  

801. 날짜를 문자로 변환하기
    1. 예제 1
             : char(current date)
               char(current time)  
               char(current date + 12 hours)

802. 문자열을 날짜로 변환하기
    1. 예제 1
           : TIMESTAMP ('2002-10-20-12.00.00.000000')
             TIMESTAMP ('2002-10-20 12:00:00')
             DATE ('2002-10-20')
             DATE ('10/20/2002')
             TIME ('12:00:00')
             TIME ('12.00.00')

999. 잡다한거

  1. 다음은 마이크로초 부분을 0으로 재설정하고 현재 타임스탬프를 얻는 방법이다
        CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS
  2. TIMESTAMP(), DATE(), TIME() 함수는 여러 포맷들을 허용한다.
  3. DATE 함수에서 쿼트를 없애면 어떻게 될까? 함수는 여전히 작동하겠지만 정확하지 않은 DATE가 나올 것이다.
         - SELECT DATE(2001-09-22) FROM SYSIBM.SYSDUMMY1;
         - 결과 : 05/24/0006
         - 이유 : 위 결과에서 어떻게 2000년 이나 차이가 났을까? DATE 함수가 입력으로 문자열을 취하면 DB2 날짜의 유효 문자로 간주되고,
                     따라서 이를 변환하는 것이다. 반대로, 입력이 숫자라면 이 함수는 현재 년도(0001-01-01)에서 1을 제한 날 수를 나타내는 것으로 간주한다.
                     위 쿼리에서 입력은 2001-09-22였고 이것은 (2001-9)-22과 동일하다. 또한 이것은 1970이다.
 4. 가끔씩, 두 개의 타임스탬프들이 어떻게 차이가 있는지 알아야 한다.
    이를 위해 DB2는 TIMESTAMPDIFF()라고 하는 빌트인 함수를 제공한다. 하지만 리턴된 값은 근사값이다.
    윤년을 고려하지 않고 한 달을 30일로만 가정하기 때문이다. 다음은 두 날짜들 간 차이를 찾아내는 방법이다.

     - timestampdiff (<n>, char( timestamp('2002-11-30-00.00.00')- timestamp('2002-11-08-00.00.00')))
        <n>의 자리에, 다음 값들을 사용하여 시간 단위를 나타낸다.
        1 = 초의 소수부분
        2 = 초
        4 = 분
        8 = 시간
        16 = 요일
        32 = 주
        64 = 달
        128 = 분기
        256 = 년
        timestampdiff()를 사용하면 날짜가 비교적 가까이 있을 때 더 정확한 결과가 나온다.
        보다 정확한 계산을 원하면 다음을 사용하여 시간 단위로(초 단위로) 차이를 결정한다.

[ DB2.일반함수 - 일자.시간.분_함수 ]

1. 현재 년/월/일/시간/분/초 개별로구하기
     연도 - sql 문장 : SELECT YEAR (current timestamp)    FROM sysibm.sysdummy1
     월    - sql 문장 : SELECT MONTH (current timestamp) FROM sysibm.sysdummy1
     일자 - sql 문장 : SELECT DAY (current timestamp)      FROM sysibm.sysdummy1
     시간 - sql 문장 : SELECT HOUR(current timestamp)      FROM sysibm.sysdummy1
     분    - sql 문장 : SELECT MINUTE(current timestamp)      FROM sysibm.sysdummy1
     초    - sql 문장 : SELECT SECOND(current timestamp)      FROM sysibm.sysdummy1
     MICROSECOND - sql 문장 : SELECT MICROSECOND(current timestamp)      FROM sysibm.sysdummy1

2. 현재 일자/시간:분:초/일자 시간:문:초 구하기
     현재일자 :                  sql 문장 : SELECT current date FROM sysibm.sysdummy1
                                                     SELECT DATE (current timestamp)  FROM sysibm.sysdummy1
                                          결과  : 2007-08-03
     현재 시간:분:초 :         sql문장 : SELECT current time FROM sysibm.sysdummy1
                                                     SELECT TIME (current timestamp)   FROM sysibm.sysdummy1
                                           결과  : 20:55:36
     현재 일자 시간:분:초 : sql문장  : SELECT current timestamp FROM sysibm.sysdummy1
                                          결과   : 2007-02-14 20:56:08.217776
     참고 : sysibm.sysdummy1 테이블은 DB2 레지스터의 값을 찾는데 사용할 수 있는 특별한 인메모리(in-memory) 테이블이다.

999. SQL 날짜와 시간 함수
      DAYNAME 인자에 요일 이름(예, 금요일)이 포함된 혼합 문자열을 리턴.  
      DAYOFWEEK 1에서 7까지 정수 값으로 요일을 나타낸다. 여기에서 1은 일요일을 의미한다.  
      DAYOFWEEK_ISO 1에서 7까지 정수 값으로 요일을 나타낸다. 여기에서 1은 월요일이다.  
      DAYOFYEAR 1에서 366 까지의 정수로 날(day)을 리턴.
      DAYS 날짜를 정수로 리턴.
      JULIAN_DAY January 1, 4712 B.C. (Julian 달력의 시작)부터 인자에 지정된 날짜 값까지 정수로 날수를 나타냄.
      MIDNIGHT_SECONDS 0에서 86400 까지의 정수 값으로 자정(midnight)과 인자에서 지정된 시간 값 사이의 초의 값을 나타냄.
      MONTHNAME 달의 이름(예를 들어, January)을 포함하고 있는 혼합 문자열 리턴.
      TIMESTAMP_ISO 날짜, 시간, 타임스탬프 인자에 기반한 타임스탬프 값을 리턴.  
      TIMESTAMP_FORMAT 문자 템플릿을 사용하여 인터프리팅 된 문자열에서 타임스탬프를 리턴.  
      TIMESTAMPDIFF 두 타임스탬프들 간 차이에 기반하여, 첫 번째 인자에서 정의된 유형의 인터벌 수를 리턴.
      TO_CHAR 문자 템플릿을 사용하여 포맷된 타임스탬프의 문자 구현을 리턴. TO_CHAR는 VARCHAR_FORMAT의 동의어이다.  
      TO_DATE 문자 템플릿을 사용하여 인터프리팅 된 문자열에서 타임스탬프를 리턴. TO_DATE는 TIMESTAMP_FORMAT의 동의어이다.
      WEEK 1에서 54까지 정수 값으로 주(week)를 리턴함. 주는 Sunday로 시작한다.  
      WEEK_ISO 1에서 53까지 정수 값으로 주를 리턴함.

999. 날짜 포맷 변경 
      날짜에 사용되는 기본 포맷은 데이터베이스의 영역(territory) 코드로 결정된다.
      (이것은 데이터베이스 생성 때 지정될 수 있다.) 예를 들어, 데이터베이스가
      territory=US를 사용하여 만들어졌다면 날짜 포맷은 다음과 같을 것이다.
      values current date
      1
      ----------
      05/30/2003
      1 record(s) selected.
      말하자면 이것은 MM/DD/YYYY 포맷이다.
      이 포맷을 변경하려면 db2 유틸리티 패키지 컬렉션을 다른 날짜 포맷으로 바인딩한다.

999. 포맷 종류
      DEF 영역 코드와 관련된 날짜 포맷과 시간 포맷을 사용.
      EUR IBM 유럽 표준의 날짜와 시간 포맷을 사용함.  
      ISO International Standards Organization의 날짜와 시간 포맷을 사용.  
      JIS Japanese Industrial Standard의 포맷 사용.  
      LOC 해당 데이터베이스의 영역 코드와 관련된 로컬 형식의 날짜와 시간 포맷을 사용함.
      USA IBM U.S. 표준을 사용함.

999. ISO (YYYY-MM-DD)로 디폴트 포맷을 변경하려면 다음과 같이 한다.
     1.명령행에서, 현재 디렉토리를 sqllib\bnd로 변경한다.
    예:
    On Windows: c:\program files\IBM\sqllib\bnd
    On UNIX: /home/db2inst1/sqllib/bnd
    SYSADM 권한을 가진 사용자로서 OS 쉘에서 데이터베이스로 연결한다.
    db2 connect to DBNAME
    db2 bind @db2ubind.lst datetime ISO blocking all grant publ

999. DB2가 로컬 포맷으로 날짜를 나타내는 방식을 어떻게 변경하는지를 설명하였다.
       하지만 'yyyymmdd'처럼 커스텀 포맷으로 하고싶다면? 최선의 방법은 커스텀 포맷팅 함수를 다시 작성하는 것이다.

       UDF:

       create function ts_fmt(TS timestamp, fmt varchar(20))
       returns varchar(50)
       return
       with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as
       (
       select substr( digits (day(TS)),9),
              substr( digits (month(TS)),9) ,
              rtrim(char(year(TS))) ,
              substr( digits (hour(TS)),9),
              substr( digits (minute(TS)),9),
              substr( digits (second(TS)),9),
              rtrim(char(microsecond(TS)))
       from sysibm.sysdummy1
       )
       select case fmt
                   when 'yyyymmdd' then yyyy || mm || dd
                   when 'mm/dd/yyyy' then mm || '/' || dd || '/' || yyyy
                   when 'yyyy/dd/mm hh:mi:ss' then yyyy || '/' || mm || '/' || dd || ' ' || hh || ':' || mi || ':' || ss
                   when 'nnnnnn' then nnnnnn
                   else 'date format ' || coalesce(fmt,' <null> ') || ' not recognized.'
              end
       from tmp
       </null>
       이 함수 코드는 복잡해보인다. 하지만 자세히 살펴보면 매우 단순하면서도 좋은 코드임을 알 수 있다.
       우선, common table expression (CTE)를 사용하여 타임스탬프(첫 번째 입력 매개변수)를 개별 컴포넌트에서 분리한다.
       그런 다음, 제공된 포맷(두 번째 입력 매개변수)을 검사하고 요청된 포맷과 부분을 사용하여 타임스탬프를 다시 정렬한다.
       이 함수는 매우 유연하다. 또 다른 패턴을 추가하려면 원하는 포맷과 함께 WHEN 구문을 붙이면 된다.
       예상하지 못했던 패턴이 나타났다면 에러메시지가 리턴된 것이다.

       사용 예제:
              values ts_fmt(current timestamp,'yyyymmdd')
              '20030818'
              values ts_fmt(current timestamp,'asa')
              'date format asa not recognized.'