USE tpch1;
select cidx, CDATE, date_format(cdate, '%Y%m') x, PERIOD_DIFF(
date_format(cdate, '%Y%m') ,'201105') y from datatypetestm order by cidx;
cidx	CDATE	x	y
1	1997-01-01	199701	-172
2	1997-01-01	199701	-172
3	1997-01-02	199701	-172
4	1997-01-03	199701	-172
5	1997-01-04	199701	-172
6	2009-12-28	200912	-17
7	2009-12-29	200912	-17
8	2009-12-30	200912	-17
9	2009-12-31	200912	-17
10	2009-12-31	200912	-17
11	2009-12-31	200912	-17
select cidx, CDATETIME, date_format(cdatetime, '%Y%m') x, PERIOD_DIFF(
date_format(cdatetime, '%Y%m') ,'201105') y from datatypetestm order by cidx;
cidx	CDATETIME	x	y
1	1997-01-01 00:00:00	199701	-172
2	1997-01-01 00:00:01	199701	-172
3	1997-01-02 00:00:01	199701	-172
4	1997-01-03 00:00:02	199701	-172
5	1997-01-04 00:00:03	199701	-172
6	2009-12-31 23:59:56	200912	-17
7	2009-12-31 23:59:57	200912	-17
8	2009-12-31 23:59:58	200912	-17
9	2009-12-31 23:59:59	200912	-17
10	2009-12-31 23:59:59	200912	-17
11	2009-12-31 23:59:59	200912	-17