DB2 a description of the application of the date and time function _db2

Dayname returns a mixed case string, with a week representing the name of the day (for example, Friday) for the day portion of the parameter.
DayOfWeek returns the number of days of the week in the argument, expressed as an integer value of 1-7, of which 1 represents Sunday.
Dayofweek_iso returns the number of days of the week in the argument, expressed as an integer value of 1-7, of which 1 represents Monday.
DayOfYear returns the day ordinal of a year in a parameter, expressed as an integer value of 1-366.
Days returns an integer representation of the date.
Julian_day returns the number of days between the specified date values in the parameter from January 1, 4712 BC (the start date of the Julian calendar), represented by an integer value.
Midnight_seconds returns the number of seconds between midnight and the time value specified in the parameter, represented by an integer value ranging from 0 to 86400.
MonthName returns a case-sensitive string (for example, January) for the month portion of the parameter.
Timestamp_iso returns a timestamp value based on a date, time, or time stamp parameter.
Timestamp_format returns a time stamp from a string that has been interpreted with a character template.
Timestampdiff The estimated slack represented by the type defined by the first parameter, based on the time difference between two timestamps.
To_char returns the character representation of a timestamp that has been formatted with a character template. To_char is synonymous with Varchar_format.
To_date returns a time stamp from a string that has been interpreted with a character template. To_date is synonymous with Timestamp_format.
WEEK returns the week ordinal of a year in a parameter, expressed as an integer value of range 1-54. Take Sunday as the beginning of a week.
Week_iso returns the week ordinal of a year in a parameter, expressed as an integer value of range 1-53.

To adjust the current time or the current time stamp to Gmt/cut, subtract the current time or time stamp from the current timezone register:
Current Time-current TimeZone
Current Timestamp-current TimeZone

Given a date, time, or time stamp, use the appropriate function to extract, if applicable, the year, month, day, time, minute, second, and microsecond sections:
Year (current timestamp)
MONTH (current timestamp)
Day (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
Microsecond (current timestamp)

Because there are no better terms, you can also use English to perform date and time calculations:
Current date + 1 year
Current date + 3 YEARS + 2 MONTHS +
Current time + 5 HOURS-3 MINUTES + SECONDS

Extracting dates and times individually from time stamps is also very simple:
DATE (current timestamp)
Time (current timestamp)

The following example describes how to obtain the current timestamp of microsecond partial zeroing:

Current Timestamp-microsecond (current TIMESTAMP) microseconds

If you want to connect a date or time value to other text, you need to convert the value to a string. To do this, just use the CHAR () function:

char (current date)
char (current time)
char (current date + hours)

To convert a string to a date or time value, you can use:

TIMESTAMP (' 2002-10-20-12.00.00.000000 ')
TIMESTAMP (' 2002-10-20 12:00:00 ')
DATE (' 2002-10-20 ')
DATE (' 10/20/2002 ')
Time (' 12:00:00 ')
Time (' 12.00.00 ')

The TIMESTAMP (), DATE (), and time () functions accept more than one format. The above formats are just examples, and I'll take it as an exercise for readers to discover other formats themselves.

Sometimes, you need to know the difference between two time stamps. To do this, DB2 provides a built-in function named Timestampdiff (). However, the function returns an approximate value because it does not consider a leap year and assumes only 30 days per month. The following example describes how to get an approximate time difference of two dates:

Timestampdiff (<n>, char (
Timestamp (' 2002-11-30-00.00.00 ')-
Timestamp (' 2002-11-08-00.00.00 '))

For <n&gt, you can use the following values to indicate the time unit of the result:

1 = fraction of seconds
2 = sec
4 = min
8 = When
16 = Day
32 = Week
64 = Month
128 = Quarterly
256 = Year
The use of Timestampdiff () when the date is very close is accurate when the date varies greatly. If you need more accurate calculations, you can use the following methods to determine the time difference (in seconds):

(t1)-Days (T2) * 86400 +
(Midnight_seconds (T1)-midnight_seconds (T2))

For convenience, you can also create SQL user-defined functions on the above methods:

CREATE FUNCTION Secondsdiff (T1 TIMESTAMP, T2 TIMESTAMP)
RETURNS INT
Return (
(t1)-Days (T2) * 86400 +
(Midnight_seconds (T1)-midnight_seconds (T2))
)
@

If you need to determine whether a given year is a leap years, here is a useful SQL function that you can create to determine the number of days in a given year:

CREATE FUNCTION daysinyear (yr INT)
RETURNS INT
Return (mod (yr.) when 0 THEN 366 ELSE
Case (mod (yr, 4)) when 0 THEN
Case (mod (yr, MB)) when 0 THEN 365 ELSE 366 End
ELSE 365 End
End) @

Finally, here is a list of built-in functions for date operations. It is designed to help you quickly identify functions that may meet your requirements, but does not provide a complete reference. For more information about these functions, please refer to the SQL reference encyclopedia.

SQL Date and Time functions
Dayname returns a mixed case string, with a week representing the name of the day (for example, Friday) for the day portion of the parameter.
DayOfWeek returns the number of days of the week in the argument, expressed as an integer value of 1-7, of which 1 represents Sunday.
Dayofweek_iso returns the number of days of the week in the argument, expressed as an integer value of 1-7, of which 1 represents Monday.
DayOfYear returns the day ordinal of a year in a parameter, expressed as an integer value of 1-366.
Days returns an integer representation of the date.
Julian_day returns the number of days between the specified date values in the parameter from January 1, 4712 BC (the start date of the Julian calendar), represented by an integer value.
Midnight_seconds returns the number of seconds between midnight and the time value specified in the parameter, represented by an integer value ranging from 0 to 86400.
MonthName returns a case-sensitive string (for example, January) for the month portion of the parameter.
Timestamp_iso returns a timestamp value based on a date, time, or time stamp parameter.
Timestamp_format returns a time stamp from a string that has been interpreted with a character template.
Timestampdiff The estimated slack represented by the type defined by the first parameter, based on the time difference between two timestamps.
To_char returns the character representation of a timestamp that has been formatted with a character template. To_char is synonymous with Varchar_format.
To_date returns a time stamp from a string that has been interpreted with a character template. To_date is synonymous with Timestamp_format.
WEEK returns the week ordinal of a year in a parameter, expressed as an integer value of range 1-54. Take Sunday as the beginning of a week.
Week_iso returns the week ordinal of a year in a parameter, expressed as an integer value of range 1-53.