DB2 Date and Time function summary

The previous article mentioned in the DB2, can through Sysibm.sysdummy1, SYSIBM. Dual gets the value in the register, or you can get the value in the register by using the VALUES keyword. In this article, we'll look at these functions directly using the VALUES keyword.

1.CURRENT Date Gets the current date, the current time gets the timestamp, current time stamp (including year, month, day, hour, minute, second), year () gets years; month () Gets the day; HOUR () Gets the Hour, MINUTE () Gets the minute, SECOND () Gets the second, date () Gets the day, time () gets the Times, and TIMESTAMP () gets the timestamp.
The above function parameter can be a datetime type, or a string in datetime format.

--Get Date:VALUESDATE ( Current TIMESTAMP);--2016/1/6VALUES  CurrentDATE;--2016/1/6--Get TimeVALUESTime ( Current TIMESTAMP);--16:40:40VALUES  Currenttime;--16:40:40--Get time stampVALUES TIMESTAMP( Current TIMESTAMP);--2016/1/6 16:40:40VALUES  Current TIMESTAMP;--2016/1/6 16:40:40VALUESSysdate;--2016/1/6 16:40:40--get Current yearValues  Year( Current timestamp);-- .--Get Current MonthValues Month( Current timestamp);--1--Get current dayValues  Day( Current timestamp);--6--Get current TimeValuesHour Current timestamp);-- ---Get minutesValuesMinute Current timestamp);-- +--Get secondsValuesSecond Current timestamp);-- +--get millisecondsValuesMicrosecond ( Current timestamp);--415269

2, DB2 time can be directly plus minus the time length of the unit.

VALUES TIMESTAMP('2016-1-06 12:18:12')+1  Year;--2017/1/6 12:18:12VALUES TIMESTAMP('2016-1-06 12:18:12')+1 MONTH;--2016/2/6 12:18:12VALUES TIMESTAMP('2016-1-06 12:18:12')+1  Day;--2016/1/7 12:18:12VALUES TIMESTAMP('2016-1-06 12:18:12')+1HOUR;--2016/1/6 13:18:12VALUES TIMESTAMP('2016-1-06 12:18:12')+1MINUTE;--2016/1/6 12:19:12VALUES TIMESTAMP('2016-1-06 12:18:12')+1SECOND;--2016/1/6 12:18:13VALUES TIMESTAMP('2016-1-06 12:18:12')+1  Year+1 MONTH+1  Day+1HOUR+1MINUTE+1SECOND;--2017/2/7 13:19:13VALUES TIMESTAMP('2016-1-06 12:18:12')-1  Year-1 MONTH-1  Day-1HOUR-1MINUTE-1SECOND;--2014/12/5 11:17:11

3, through days () can be obtained two time difference of day.

VALUES Days (current date)–days ( Current date–day); --

4, the PASS function can obtain the time corresponding to the week (month).

--Dayname () returns the day of the week for the specified date, which is an English name consisting of the first character uppercase and other lowercase characters. ValuesDayname ( Current timestamp);--Wednesday (Friday on the same day)--DAYOFWEEK () returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Sunday. ValuesDAYOFWEEK ( Current timestamp);--4 (Wednesday on the same day)--Dayofweek_iso () returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Monday. ValuesDayofweek_iso ( Current timestamp);--3 (currently Wednesday)--DayOfYear () returns the day ordinal of a year in a parameter, expressed as an integer value with a range of 1-366. ValuesDayOfYear ( Current timestamp);--6--MONTHNAME () returns a mixed-case string (for example, January) for months of the month portion of the parameter. ValuesMONTHNAME ( Current TIMESTAMP);--January (currently January)--WEEK () returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-54. Take Sunday as the beginning of the week. (parameter can be a date format or a date format string)VALUESWEEK ('2016-01-02');--1VALUESWEEK ('2016-01-03');--2--Week_iso () returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-54. Take Monday as the beginning of the week. (parameter can be a date format or a date format string)VALUESWeek_iso ('2016-01-02');-- -VALUESWeek_iso ('2016-01-03');-- -VALUESWeek_iso ('2016-01-04');--1

5, Timestampdiff (N,char (' TIMESTAMP (' 2012-05-25 10:23:24 ')-timestamp (' 2012-05-25 10:20:24 '))), based on the time difference between two timestamps, Returns the estimated time difference represented by the type defined by the first parameter. (divisible portions, not rounded )

n=2: Seconds; n=4: min; n=8: time;

N=16: Days; n=32: Zhou; n=64: month;

N=128: Quarterly; n=256: Year;

VALUESTimestampdiff (2,CHAR(TIMESTAMP('2016-01-06 12:23:24')-TIMESTAMP('2016-01-06 12:20:24')));--180 secondsVALUESTimestampdiff (4,CHAR(TIMESTAMP('2016-01-06 12:23:24')-TIMESTAMP('2016-01-06 10:20:22')));--123 minutesVALUESTimestampdiff (8,CHAR(TIMESTAMP('2016-01-06 12:23:24')-TIMESTAMP('2016-01-06 10:20:22')));--2 hoursVALUESTimestampdiff ( -,CHAR(TIMESTAMP('2016-01-07 12:23:24')-TIMESTAMP('2016-01-06 15:20:22')));--0 DaysVALUESTimestampdiff ( -,CHAR(TIMESTAMP('2016-01-07 12:23:24')-TIMESTAMP('2016-01-06 10:20:22')));--1 daysVALUESTimestampdiff ( +,CHAR(TIMESTAMP('2016-03-06 12:23:24')-TIMESTAMP('2016-01-06 10:20:22')));--8 weeksVALUESTimestampdiff ( -,CHAR(TIMESTAMP('2016-03-06 12:23:24')-TIMESTAMP('2016-01-06 10:20:22')));--FebruaryVALUESTimestampdiff ( -,CHAR(TIMESTAMP('2016-05-06 10:20:22')-TIMESTAMP('2016-02-06 10:20:22')));--1 QuarterVALUESTimestampdiff ( the,CHAR(TIMESTAMP('2017-01-06 10:20:22')-TIMESTAMP('2016-01-06 10:20:22')));--1 Years

DB2 Date and Time function summary