DB2 Date Time function

Source: Internet
Author: User
Tags db2 db2 connect db2 connect to db2 date mixed rtrim

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.