DB2 Time Functions

DB2 time functions are one of our most common functions. The following describes some DB2 time functions for your reference. We hope you can learn more about DB2 time functions.

 
 

  
  
-- Get the current date:
  
  
 
  
  
Select current date from sysibm. sysdummy1;
  
  
Values current date;
  
  
 
  
  
-- Get the current date
  
  
Select current time from sysibm. sysdummy1;
  
  
Values current time;
  
  
-- Get the current Timestamp
  
  
Select current timestamp from sysibm. sysdummy1;
  
  
Values current timestamp;
  
  
 
  
  
-- To adjust the current time or current time stamp to GMT/CUT, subtract the current time or time stamp from the current time zone register:
  
  
 
  
  
Values current time-current timezone;
  
  
Values current timestamp-current timezone;
  
  
 
  
  
-- Get the current year
  
  
 
  
  
Values year (current timestamp );
  
  
 
  
  
-- Get the current month
  
  
Values month (current timestamp );
  
  
 
  
  
-- Get the current day
  
  
Values day (current timestamp );
  
  
 
  
  
-- Get current time
  
  
Values hour (current timestamp );
  
  
 
  
  
-- Get minutes
  
  
Values minute (current timestamp );
  
  
 
  
  
-- Get seconds
  
  
Values second (current timestamp );
  
  
 
  
  
-- Get millisecond
  
  
Values microsecond (current timestamp );
  
  
 
  
  
-- Extract the date and time from the time stamp
  
  
 
  
  
Values date (current timestamp );
  
  
Values VARCHAR_FORMAT (current TIMESTAMP, 'yyyy-mm-dd ');
  
  
Values char (current date );
  
  
Values time (current timestamp );
  
  
 
  
  
-- Calculate the execution date and time
  
  
 
  
  
Values current date + 1 year;
  
  
Values current date + 3 years + 2 months + 15 days;
  
  
Values current time + 5 hours-3 minutes + 10 seconds;
  
  
 
  
  
-- Calculate the number of days between two dates
  
  
 
  
  
Values days (current date)-days (date ('1970-02-20 '));
  
  
 
  
  
-- Replace time and date with a string
  
  
 
  
  
Values char (current date );
  
  
Values char (current time );
  
  
 
  
  
-- Converts a string to a date or time value.
  
  
 
  
  
Values timestamp ('2017-03-09-22.43.00.000000 ');
  
  
Values timestamp ('2017-03-09 22:44:36 ');
  
  
Values date ('2017-03-09 ');
  
  
Values date ('2014/1/123 ');
  
  
Values time ('22: 45: 27 ');
  
  
Values time ('22. 45.27 ');
  
  
 
  
  
-- Calculate the time difference between two timestamps:
  
  
 
  
  
-- The decimal part of the second is in unit
  
  
Values timestampdiff (1, char (current timestamp-timestamp ('2017-01-01-00.00.00 ')));
  
  
-- Seconds
  
  
Values timestampdiff (2, char (current timestamp-timestamp ('2017-01-01-00.00.00 ')));
  
  
-- Unit
  
  
Values timestampdiff (4, char (current timestamp-timestamp ('2017-01-01-00.00.00 ')));
  
  
-- Hours
  
  
Values timestampdiff (8, char (current timestamp-timestamp ('2017-01-01-00.00.00 ')));
  
  
-- Days
  
  
Values timestampdiff (16, char (current timestamp-timestamp ('2017-01-01-00.00.00 ')));
  
  
-- Weekly
  
  
Values timestampdiff (32, char (current timestamp-timestamp ('2017-01-01-00.00.00 ')));
  
  
-- Month
  
  
Values timestampdiff (64, char (current timestamp-timestamp ('2017-01-01-00.00.00 ')));
  
  
-- Quarterly
  
  
Values timestampdiff (128, char (current timestamp-timestamp ('2017-01-01-00.00.00 ')));
  
  
-- Year
  
  
Values timestampdiff (256, char (current timestamp-timestamp ('2017-01-01-00.00.00 ')));
 
 

DB2 common table expression usage

Cross-database access to DB2 tables

DB2 tablespace size limit

Deep understanding of DB2 stored procedures

DB2 connection Server Configuration