DB2 Common time Operation __DB2

--Please note that when using, please replace the empty space, the text of the blank
--Get Current date:
Select Current date from sysibm.sysdummy1;
Values current date;
--Get Current date
Select current time from Sysibm.sysdummy1;
Values current time;
--Get the current time stamp
Select current Timestamp from sysibm.sysdummy1;
Values current timestamp;
--To adjust the current time or current timestamp to gmt/cut, subtract the current time or time stamp from the current timezone register:
Values current time-current timezone;
Values current timestamp-current timezone;
--Get current year
Values year (current timestamp);
--Get current month
Values month (current timestamp);
--Get current day
Values Day (current timestamp);
--Gets the current time
Values hour (current timestamp);
--Get Minutes
Values minute (current timestamp);
--Get seconds
Values second (current timestamp);
--Get milliseconds
Values Microsecond (current timestamp);
--Extract date and time separately from time stamp
Values date (current timestamp);
Values Varchar_format (current TIMESTAMP, ' yyyy-mm-dd ');
Values char (current date);
Values time (current timestamp);
--Calculation of execution date and time
Values current date+1 year;
Values current date+3 years+2 months for +15 days;
Values current time +5 hours-3 minutes +10 seconds;
--Calculates the number of days between two dates
Values days (today date)-Days (' 2010-02-20 ');
--time and date replaced by string
Values char (current date);
Values char (current time);
--to convert a string to a date or time value
Values timestamp (' 2010-03-09-22.43.00.000000 ');
Values timestamp (' 2010-03-09 22:44:36 ');
Values date (' 2010-03-09 ');
Values date (' 03/09/2010 ');
Values time (' 22:45:27 ');
Values time (' 22.45.27 ');
--Calculates the time difference between two timestamps:
--The number of seconds is divided into units
Values Timestampdiff (1,char) (Current Timestamp-timestamp (' 2010-01-01-00.00.00 '));
--seconds as a unit
Values Timestampdiff (2,char) (Current Timestamp-timestamp (' 2010-01-01-00.00.00 '));
--Divided into units
Values Timestampdiff (4,char) (Current Timestamp-timestamp (' 2010-01-01-00.00.00 '));
--hours as a unit
Values Timestampdiff (8,char) (Current Timestamp-timestamp (' 2010-01-01-00.00.00 '));
--Days as a unit
Values Timestampdiff (16,char) (Current Timestamp-timestamp (' 2010-01-01-00.00.00 '));
--Week as unit
Values Timestampdiff (32,char) (Current Timestamp-timestamp (' 2010-01-01-00.00.00 '));
--month as unit
Values Timestampdiff (64,char) (Current Timestamp-timestamp (' 2010-01-01-00.00.00 '));
-Quarter as Unit
Values Timestampdiff (128,char) (Current Timestamp-timestamp (' 2010-01-01-00.00.00 '));
--year as unit
Values Timestampdiff (256,char) (Current Timestamp-timestamp (' 2010-01-01-00.00.00 '));




Comparison of date and time functions for SQL Server, Oracle, and DB2
1, on the basis of the date minus one day
Sql:
DATEADD (Day,-1,convert (smalldatetime,returndate))
Oarcle:
To_char (To_date (returndate, ' Yyyy-mm-dd ')-1, ' YYYY-MM-DD ')
DB2:
char (date (returndate)-1 days)


Cases:
Select char (select StartDate from Xj_task where taskid=22) + 1 days) from Sysibm.sysdummy1; --Day plus 1


Select char (select StartTime from Xj_task where taskid=22) + 2 hours) from sysibm.sysdummy1; --hour plus 2


Select char (select StartTime from Xj_task where taskid=22) + minutes) from sysibm.sysdummy1; --Minute plus 30


——————————-
2, on the basis of date and time minus one day
Sql:
DATEADD (Day,-1,convert (smalldatetime,enddate+ ' +endtime))
Oralce:
To_char (To_date (concat (EndDate, '), endtime), ' Yyyy-mm-dd HH24:MI:SS ')-1, ' Yyyy-mm-dd HH24:MI:SS ')
DB2:
char (TIMESTAMP (Concat (concat (Begindate, '), BeginTime))-1 days
——————————-
3. Calculate the number of days between two days
Sql:
Convert (datetime,t1.lastoperatedate)-convert (datetime,t1.createdate)
Oracle:
To_date (t1.lastoperatedate, ' Yyyy-mm-dd ')-to_date (t1.createdate, ' yyyy-mm-dd ')
DB2:
Days (date (lastoperatedate))-days (date (createdate))
If you want to add to the date or date and time on the basis of the year, month, day, time, minutes, seconds, can be based on the above examples to make the corresponding changes, the use of specific methods are very similar.