Date and time function, and applications in DB2

xiaoxiao2021-03-06 66

DayName returns a case where the mixed string is used, and the day of the parameter is used to represent this day (for example, Friday).

Dayofweek returns the week a day in the parameter, and the range of integers of 1-7 is represented in the Sunday.

Dayofweek_iso Returns the week in the parameter, expressed in the range of 1-7 integral, where 1 is Monday.

DayOfyear returns the first few days in the year in the parameter, expressed in the range of 1-366 integer value.

Days return the full representation of the date.

Julian_Day returns the number of days between January 1, 4712 (the beginning of the Confucian calendar) to the number of days between the specified date values in the parameter, and is expressed in an integer value.

MidNight_Seconds Returns the second number between the time values specified in the midnight and parameters, indicating the integer value between 0 and 86400.

Monthname returns a case-in-case mixed string (for example, January) for the month of the monthly part of the parameter.

TimeStamp_ISO returns a timestamp value based on the date, time or timestamp parameters.

TimeStamp_Format returns a timestamp from a string that has been interpreted using the character template.

The timestampdiff returns the estimation time difference indicated by the type defined by the first parameter according to the time difference between the two time stamps.

TO_CHAR Returns the character representation of the timestamp that has been formatted with the character template. TO_CHAR is synonymous with varchar_format.

TO_DATE returns the timestamp from the string that has been interprected using the character template. TO_DATE is synonymous with TimeStamp_Format.

WEEK returns a few weeks in the parameter in the first few years, expressed in the range of 1-54 integer value. The beginning of the week as a week.

Week_ISO returns a few weeks in the parameter, expressed in the range of 1-53 integer values.

To adjust the current time or the current timestamp to GMT / CUT, reduce the current time or timestamp to the current time zone register:

Current Time - Current Timezone

Current TimeStamp - Current Timezone

The date, time or timestamp is given, and the appropriate function can be extracted (if applicable), month, day, time, minute, second and microseconds:

Year (Current TimeStamp)

Month (current TimeStamp)

Day (current timestamp)

Hour (Current TimeStamp)

Minute (Current TimeStamp)

Second (Current TimeStamp)

Microsecond (Current TimeStamp)

Because there is no better term, you can also use English to perform dates and time calculations:

Current Date 1 Year

Current Date 3 Years 2 Months 15 Days

Current Time 5 Hours - 3 minutes 10 seconds

The date and time from timestock separate extraction is also very simple:

Date (Current TimeStamp)

Current TimeStamp

The following example describes how to get a microsecond part of the current timestamp: Current TimeStamp - Microseconds

If you want to connect the date or time value with other text, then you need to convert the value into a string. To this end, just use the char () function:

CHAR (CURRENT DATE)

CHAR (CURRENT TIME)

CHAR (Current Date 12 Hours)

To convert a string into 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 ')

TimeStamp (), Date () and Time () functions accept more formats. The above format is just an example, I will use it as an exercise, let the reader discovers other formats.

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

Timestampdiff (, char)

TimeStamp ('2002-11-30-00.00.00') -

TimeStamp ('2002-11-08-00.00.00'))))))

For , the following values can be used to indicate the time unit of the result:

1 = second fraction

2 = second

4 = points

8 =

16 = day

32 = week

64 = month

128 = quarter

256 = year

TimeStampDiff () is very accurate when the date is very similar to the date when the date is very close. If you need more accurate calculations, you can use the following method to determine the time difference (in seconds):

(Days (T1) - Days (t2)) * 86400

(Midnight_Seconds (T1) - Midnight_Seconds (T2))

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

Create Function SecondsDiff (T1 TIMESTAMP, T2 TIMESTAMP)

Returns Int

Return

(Days (T1) - Days (t2)) * 86400

(Midnight_Seconds (T1) - Midnight_Seconds (T2))

)

If you need to determine if a given year is a leap year, the following is a very useful SQL function, you can create it to determine the number of days from the given year:

Create Function Daysinyear (YR INT)

Returns Int

Return (Case (MOD (YR, 400)) when 366 else

Case (MOD (YR, 4)) when 0 THEN

Case (MOD (YR, 100)) WHEN 0 THEN 365 ELSE 366 END

ELSE 365 END

End) @

Finally, the following is a built-in function table for date operation. It is designed to help you quickly determine the functions that may meet your requirements, but no complete reference is available. For more information on these functions, please refer to the SQL reference. SQL date and time function