DB2 Date Time function

DB2 Date Time function

(DATE (TRIM (CHAR (dt#11y)) | | -' | | TRIM (CHAR (dt#11m)) | | ' -' | | TRIM (CHAR (dt#11d))) between date (' & StrDate1 & ') and date (' & StrDate2 & '))

(Y > Y) or ((y = y) and (M > m)) or ((y = y) and (M = m) and (d >= D))

Basis

To use SQL to obtain the current date, time, and time stamp, refer to the appropriate DB2 register:

SELECT current date FROM sysibm.sysdummy1 SELECT current time FROM sysibm.sysdummy1 SELECT current timestamp FROM sysibm.sysdummy1

The sysibm.sysdummy1 table is a special in-memory table that can be used to find the value of the DB2 register as shown above. You can also use the keyword values to evaluate a register or an expression. For example, on the DB2 command line PROCESSOR,CLP, the following SQL statement reveals similar information:

VALUES current date VALUES current time VALUES current timestamp

In the remaining example, I will only provide a function or an expression, and no longer repeatSELECT ... FROM sysibm.sysdummy1or use the VALUES clause.

To adjust the current time or current timestamp 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, the appropriate function can be used to extract (if applicable) the parts of the year, month, day, hour, minute, second, and microsecond separately:

YEAR (current timestamp) MONTH (current timestamp) DAY (current timestamp) HOUR (current timestamp) MINUTE (current timestamp) SECOND (current timestamp) MICROSECOND (current timestamp)

It's also easy to extract the date and time separately from the timestamp:

DATE (current timestamp) TIME (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 + 15 DAYS current time + 5 HOURS - 3 MINUTES + 10 SECONDS

To calculate the number of days between two dates, you can subtract the dates as follows:

Days (current date) – Days (date (' 1999-10-22 '))

The following example describes how to get the current time stamp for a microsecond partial zeroing:

Current Timestamp-microsecond (current TIMESTAMP) microseconds

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

char(current date) char(current time) char(current date + 12 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 many more formats. The above several formats are just examples, and I'll use it as an exercise to let the reader find other formats on their own.

Warning: excerpt from DB2 UDB V8.1 SQL Cookbook, author Graeme birchall (see http://ourworld.compuserve.com/homepages/graeme_birchall ). What if you accidentally omit the quotation marks in a date function? The conclusion is that the function will work, but the result will be error: select DATE (2001-09-22) from SYSIBM. SYSDUMMY1; Results: ======05/24/0006 Why does it have a gap of nearly 2000 years? When the date function obtains a string as an input parameter, it assumes that it is a valid representation of the DB2 date and converts it appropriately. Conversely, when the input parameter is a numeric type, the function assumes that the parameter value minus 1 equals the number of days from the first day of the Year (0001-01-01). In the example above, our input is 2001-09-22, which is understood as (2001-9)-22, equals 1970 days, so the function is interpreted as DATE (1970).

Date function

Sometimes, you need to know the difference between two time stamps. To do this, DB2 provides a built-in function called 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>, you can use the following values instead to indicate the time unit of the result:

• 1 = decimal part of seconds
• 2 = Seconds
• 4 = Minute
• 8 = When
• 16 = Day
• 32 = Week
• 64 = Month
• 128 = Quarter
• 256 = Year

Use Timestampdiff () when the date is close enough to be accurate when the date is significantly different. If more accurate calculations are needed, you can use the following methods to determine the difference in seconds:

(DAYS(t1) - DAYS(t2)) * 86400 +  
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

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

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 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 (CASE (mod(yr, 400)) WHEN 0 THEN 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 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, refer to the SQL reference encyclopedia.

SQL Date and Time functions
Dayname	Returns a mixed-case string that represents the day's name for the day part of the parameter (for example, Friday).
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.
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.
DayOfYear	Returns the day ordinal of a year in a parameter, expressed as an integer value with a range of 1-366.
Days	Returns an integer representation of the date.
Julian_day	Returns the number of days from January 1, 4712 BC (the start date of the Julian calendar) to the specified date value in the parameter, expressed as an integer value.
Midnight_seconds	Returns the number of seconds between midnight and the time value specified in the parameter, expressed as an integer value ranging from 0 to 86400.
MONTHNAME	For months of the month portion of the parameter, returns a mixed-case string (for example, January).
Timestamp_iso	Returns a timestamp value based on a date, time, or timestamp parameter.
Timestamp_format	Returns a timestamp from a string that has been interpreted using a character template.
Timestampdiff	Returns the estimated time difference represented by the type defined by the first parameter, based on the difference between two timestamps.
To_char	Returns a character representation of the timestamp that has been formatted with a character template. To_char is synonymous with Varchar_format.
To_date	Returns a timestamp from a string that has been interpreted using 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 with a range of 1-54. Take Sunday as the beginning of the week.
Week_iso	Returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-53.

Change the date format
In terms of the representation of the date, this is also a problem that I often encounter. The default format for dates is determined by the database's region code, which is specified when the database is created. For example, when I use territory=us to define a region code when I create a database, the format of the date will look like this:

values current date 1 ---------- 05/30/2003 1 record(s) selected.

In other words, the format of the date is mm/dd/yyyy. If you want to change this format, you can do so by binding a specific DB2 toolkit. Other supported date formats include:

Def	Use date and time formats that match the region code.
EUR	Use the IBM standard format for European date and time.
Iso	Use the date and time format established by the International Standards Organization (ISO).
Jis	Date and time formats using the Japanese industry standard.
LOC	Use the local date and time format that matches the database region code.
USA	IBM standard format with us date and time.

In a Windows environment, to convert the default date and time format to ISO format (YYYY-MM-DD), do the following:

1. In the command line, change the current directory tosqllib\bnd.

   For example:
   in the Windows environment: c:\program files\IBM\sqllib\bnd
   in the UNIX environment: /home/db2inst1/sqllib/bnd

2. Connect to the database from the command line interface of the operating system with users with SYSADM permissions:

   db2 connect to DBNAME db2 bind @db2ubind.lst datetime ISO blocking all grant public

   (in your actual environment, replace the DBNAME and ISO with your database name and the date format you want to use.) )

Now you can see that your database is already using ISO as the date format:

values current date 1 ---------- 2003-05-30 1 record(s) selected.

Custom Date/Time formats
In the example above, we showed how to convert the DB2 current date format into a specific format supported by the system. But what if you want to convert the current date format into a custom format (such as ' YYYYMMDD ')? In my experience, the best way is to write a custom format function.

Here is the code for this UDF:

create function ts_fmt(TS timestamp, fmt varchar(20)) returns varchar(50) return with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as ( select substr( digits (day(TS)),9), substr( digits (month(TS)),9) , rtrim(char(year(TS))) , substr( digits (hour(TS)),9), substr( digits (minute(TS)),9), substr( digits (second(TS)),9), rtrim(char(microsecond(TS))) from sysibm.sysdummy1 ) select case fmt when ‘yyyymmdd‘ then yyyy || mm || dd when ‘mm/dd/yyyy‘ then mm || ‘/‘ || dd || ‘/‘ || yyyy when ‘yyyy/dd/mm hh:mi:ss‘ then yyyy || ‘/‘ || mm || ‘/‘ || dd || ‘ ‘ || hh || ‘:‘ || mi || ‘:‘ || ss when ‘nnnnnn‘ then nnnnnn else ‘date format ‘ || coalesce(fmt,‘ <null> ‘) || ‘ not recognized.‘ end from tmp

At first glance, the code of the function may seem complicated, but after careful study you will find that the code is very simple and elegant. At first, we used a common table expression (CTE) to split a timestamp (the first input parameter) into a separate time element. We then examine the provided custom format (the second input parameter) and combine the previously stripped elements with the requirements of that custom format.

This function is also very flexible. If you want to add another pattern, you can easily do so by adding a when clause. During use, the function returns an error message if the user-supplied format does not conform to any of the patterns defined in the When clause.

Examples of Use methods:

values ts_fmt(current timestamp,‘yyyymmdd‘)
 ‘20030818‘
values ts_fmt(current timestamp,‘asa‘) 
 ‘date format asa not recognized.‘

DB2 Date Time function