[2k] 날짜(일자) 데이터 입력 형식에 따른 출력 변화 및 상이한 조회 결과

1. /**********************************************************************************************
   -- Title : [2k] 날짜(일자) 데이터 입력 형식에 따른 출력 변화 및 상이한 조회 결과
   -- Reference : dBRang.com
   -- Key word : cast
   **********************************************************************************************/
2. /*************************************
   -- 데이터 입력 형식에 따른 출력 변화
   *************************************/
3. create table #a (c datetime)
4. insert #a values ('1753.1.1')
   insert #a values ('17530101')
   insert #a values (0)
   insert #a values (1)
   insert #a values (2000)
   insert #a values (1989)
5. select * from #a
   /*
   1753-01-01 00:00:00.000
   1753-01-01 00:00:00.000
   1900-01-01 00:00:00.000
   1900-01-02 00:00:00.000
   1905-06-24 00:00:00.000
   1905-06-13 00:00:00.000
   */
6. select * from #a where c = '17530101'
   /*
   1753-01-01 00:00:00.000
   1753-01-01 00:00:00.000
   */
7. select * from #a where c = 1989
   --1905-06-13 00:00:00.000
8. select * from #a where year(c) = 1753
   /*
   1753-01-01 00:00:00.000
   1753-01-01 00:00:00.000
   */
9. -- 00.003초 간격으로 increation 확인
   SELECT CAST('2000-05-08 12:35:59.000' AS datetime) union all
   SELECT CAST('2000-05-08 12:35:59.001' AS datetime) union all
   SELECT CAST('2000-05-08 12:35:59.002' AS datetime) union all
   SELECT CAST('2000-05-08 12:35:59.003' AS datetime) union all
   SELECT CAST('2000-05-08 12:35:59.004' AS datetime) union all
   SELECT CAST('2000-05-08 12:35:59.999' AS datetime)
   /*
   2000-05-08 12:35:59.000
   2000-05-08 12:35:59.000
   2000-05-08 12:35:59.003
   2000-05-08 12:35:59.003
   2000-05-08 12:35:59.003
   2000-05-08 12:36:00.000
   */
10. /*************************************
    -- 날짜 형식에 따른 상이한 조회 결과
    *************************************/
11. use credit
12. -- 쿼리 1.
    select lastname, firstname, corp_name, charge_dt,  charge_amt
    from member m join corporation c
                  on m.corp_no = c.corp_no
                  join charge ch
                  on ch.member_no = m.member_no
    where year(charge_dt) = 2005
    and month(charge_dt) = 9
    order by charge_dt
    -- 1525건
    -- 23:59:59.999 까지 포함한다.
    -- charge_dt에서 scan한다.
13. -- 쿼리 2.
    select ch.charge_no, lastname, firstname, corp_name, charge_dt,  charge_amt
    from member m join corporation c
                  on m.corp_no = c.corp_no
                  join charge ch
                  on ch.member_no = m.member_no
    where charge_dt >= '2005.9.1' and charge_dt <= '2005.9.30'
    order by charge_dt
    -- 1424건
    -- 23:59:59.999 까지 포함 안된다.(2005.09.03.00:00:000 까지 해서 그런가?)
    -- charge_dt에서 seek한다.
14. -- 쿼리 3.
    select lastname, firstname, corp_name, charge_dt,  charge_amt
    from member m join corporation c
                  on m.corp_no = c.corp_no
                  join charge ch
                  on ch.member_no = m.member_no
    where charge_dt between '2005.9.1' and '2005.9.30 23:59:59.999'
    order by charge_dt
    -- 1525건
    -- 23:59:59.999 까지 포함한다.
    -- charge_dt에서 seek한다.