What happens if you accidentally leave out the quotes in the DATE function? The function still works, but the result is not correct:
Advertisements
Select Date Query
SELECT DATE(2001-09-22) FROM SYSIBM.SYSDUMMY1; Answer: ====== 05/24/0006=====
Why the 2,000-year difference in the above results? When the DATE function gets a character string as input, it assumes that it is a valid character representation of a DB2 Date, and converts it accordingly. By contrast, when the input is numeric, the function assumes that it represents the number of days minus one from the start of the current era (that is, 0001-01-01). In the above query, the input was 2001-09-22, which equals (2001-9)-22, which equals 1970 days.
Date functions
- Sometimes, you need to know how the difference between two timestamps. For this, DB2 provides a built-in function called TIMESTAMPDIFF().
- The value returned is an approximation, however, because it does not account for leap years and assumes only 30 days per month.
How to get Current Date SQL Query
SELECT AVG(YEAR(CURRENT DATE - BIRTHDATE))
FROM DSN8A10.EMP;
How to get Current Time SQL Query
SELECT DSN8A10.PROJACT.*, CURRENT DATE, CURRENT TIME
FROM DSN8A10.PROJACT;
Here is an example of how to find the approximate difference in time between two dates
timestampdiff (, char( timestamp('2002-11-30-00.00.00')-
timestamp('2002-11-08-00.00.00')))
In place of, use one of the following values to indicate the unit of time for the result
1 = Fractions of a second 2 = Seconds 4 = Minutes 8 = Hours 16 = Days 32 = Weeks 64 = Months 128 = Quarters 256 = Years
Using timestampdiff() is more accurate when the dates are close together than when they are far apart. If you need a more precise calculation, you can use the following to determine the difference in time (in seconds):
(DAYS(t1) - DAYS(t2)) * 86400 + (MIDNIGHT_SECONDS(t1) -
MIDNIGHT_SECONDS(t2))
For convenience, you can also create an SQL user-defined function of the above
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, here is a useful SQL function 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, here is a chart of built-in functions for Date manipulation. The intent is to help you quickly identify a function that might fit your needs, not to provide a full reference.
SQL Date and Time Functions
- DAYNAME – Returns a mixed case character string containing the name of the day (e.g., Friday) for the day portion of the argument.
- DAYOFWEEK – Returns the day of the week in the argument as an integer value in the range 1-7, where 1 represents Sunday.
- DAYOFWEEK_ISO – Returns the day of the week in the argument as an integer value in the range 1-7, where 1 represents Monday.
- DAYOFYEAR – Returns the day of the year in the argument as an integer value in the range 1-366.
- DAYS – Returns an integer representation of a date.
- JULIAN_DAY – Returns an integer value representing the number of days from January 1, 4712 B.C. (the start of Julian date calendar) to the date value specified in the argument.
- MIDNIGHT_SECONDS – Returns an integer value in the range 0 to 86 400 representing the number of seconds between midnight and the time value specified in the argument.
- MONTHNAME – Returns a mixed case character string containing the name of the month (e.g., January) for the month portion of the argument.
- TIMESTAMP_ISO – Returns a timestamp value based on date, time or timestamp argument.
- TIMESTAMP_FORMAT – Returns a timestamp from a character string that has been interpreted using a character template.
- TIMESTAMPDIFF – Returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.
- TO_CHAR – Returns a character representation of a timestamp that has been formatted using a character template. TO_CHAR is a synonym for VARCHAR_FORMAT.
- TO_DATE – Returns a timestamp from a character string that has been interpreted using a character template. TO_DATE is a synonym for TIMESTAMP_FORMAT.
- WEEK – Returns the week of the year of the argument as an integer value in range 1-54. The week starts with Sunday.
- WEEK_ISO – Returns the week of the year of the argument as an integer value in the range 1-53.
Changing the date format
A common question I get often relates to the presentation of dates. The default format used for dates is determined by the territory code of the database (which can be specified at database creation time). For example, my database was created using territory=US.
Therefore the date format looks like the following:
values current date 1 ---------- 05/30/2003 1 record(s) selected.
That is, the format is MM/DD/YYYY. If you want to change the format, you can bind the collection of db2 utility packages to use a different date format. The formats supported are:
- DEF – Use a date and time format associated with the territory code.
- EUR – Use the IBM standard for Europe date and time format.
- ISO – Use the date and time format of the International Standards Organization.
- JIS – Use the date and time format of the Japanese Industrial Standard.
- LOC – Use the date and time format in local form associated with the territory code of the database.
- USA – Use the IBM standard for U.S. date and time format.
To change the default format to ISO on windows (YYYY-MM-DD), do the following steps:
- On the command line, change your current directory to
sqllibbnd.For example:
On Windows:c:program filesIBMsqllibbnd
On UNIX:/home/db2inst1/sqllib/bnd
- Connect to the database from the operating system shell as a user with SYSADM authority:
db2 connect toDBNAME db2 bind @db2ubind.lst datetime ISO blocking all grant public(In your case, substitute your database name and desired date format for DBNAME and ISO, respectively.)
Now, you can see that the database uses ISO date format:
values current date 1 ———- 2003-05-30 1 record(s) selected.
Custom Date/Time Formatting
- In the last example, we demonstrated how to change the way DB2 presents dates in some localized formats. But what if you wish to have a custom format such as ‘yyyymmdd’?
- The best way to do this is by writing your own custom formatting function.
User defined function
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,'') || ' not recognized.'
end from tmpExplanation for the above UDF
- The function code may appear complex at first, but upon closer examination, you’ll see that it is actually quite simple and elegant. First, we use a common table expression (CTE) to strip apart a timestamp (the first input parameter) into its individual components. From there, we check the format provided (the second input parameter) and reassemble the timestamp using the requested format and parts.
- The function is also very flexible. To add another pattern simply append another WHEN clause with the expected format. When an unexpected pattern is encountered, an error message is returned.
Usage examples
values ts_fmt(current timestamp,'yyyymmdd') '20030818' values ts_fmt(current timestamp,'asa') 'date format asa not recognized.'
Read More
Related Posts
Srinimf 