| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
09-23-09, 03:05
|
|
Registered User
|
|
Join Date: Sep 2009
Posts: 2
|
|
|
date functions help
|
|
Hi DB2geeks!
I am new to DB2 practising in DB2 v 8.0 Windows Environment.  Part of the table looks like as follows:-
doi(column) dot(column)
04/10/1996 05/10/1996
04/12/1996 05/05/1996
04/21/1996 05/15/1996
04/25/1996 05/25/1996
04/30/1996 05/22/1996
Request to provide me the solution for the following questions?
1)Display the last date of the month in dot
2)Display the months between the dot & doi
3)Display the next occurence of "friday" to the dot
4)Display the rounded date in the year format from the day in dot
|
|
09-23-09, 04:46
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Here is a good article:
DB2 Basics: Fun with Dates and Times
an example for your request:
Code:
------------------------------ Commands Entered ------------------------------
WITH
sample_data(doi, dot) AS (
SELECT DATE(doi), DATE(dot)
FROM (VALUES
('04/10/1996', '05/10/1996')
,('04/12/1996', '05/05/1996')
,('04/21/1996', '05/15/1996')
,('04/25/1996', '05/25/1996')
,('04/30/1996', '05/22/1996')
) AS s(doi, dot)
)
SELECT doi, dot
, dot + 1 MONTH - DAY(dot + 1 MONTH) DAYS
AS "Last date of month"
, TIMESTAMPDIFF(64, CHAR(TIMESTAMP(dot, '00:00:00') - TIMESTAMP(doi, '00:00:00')))
AS "Months between"
, dot + MOD(13 - DAYOFWEEK(dot), 7) DAYS
AS "Next Friday"
, DAYOFYEAR(dot)
AS "Date in year"
FROM sample_data
;
------------------------------------------------------------------------------
DOI DOT Last date of month Months between Next Friday Date in year
---------- ---------- ------------------ -------------- ----------- ------------
1996-04-10 1996-05-10 1996-05-31 1 1996-05-10 131
1996-04-12 1996-05-05 1996-05-31 0 1996-05-10 126
1996-04-21 1996-05-15 1996-05-31 0 1996-05-17 136
1996-04-25 1996-05-25 1996-05-31 1 1996-05-31 146
1996-04-30 1996-05-22 1996-05-31 0 1996-05-24 143
5 record(s) selected.
I afraid that I misunderstood your requirements, because of my poor English capability.
If you want another results, please supply your required results.
DB2 V8 for Windows is obsolete.
Here is newest DB2 Express-C:
Download : IBM DB2 Express-C 9
|
|
09-23-09, 07:04
|
|
Registered User
|
|
Join Date: Jun 2002
Posts: 12
|
|
|
|
Last day of month and next day can also be expressed as
Code:
WITH
sample_data(doi, dot) AS (
SELECT DATE(doi), DATE(dot)
FROM (VALUES
('04/10/1996', '05/10/1996')
,('04/12/1996', '05/05/1996')
,('04/21/1996', '05/15/1996')
,('04/25/1996', '05/25/1996')
,('04/30/1996', '05/22/1996')
) AS s(doi, dot)
)
select doi, dot
,NEXT_DAY(dot, 'Friday') as "Next Friday"
,LAST_DAY(dot) as "Last date of month"
from sample_data
Code:
DOI DOT Next Friday Last date of month
---------- ---------- ----------- ------------------
04/10/1996 05/10/1996 05/17/1996 05/31/1996
04/12/1996 05/05/1996 05/10/1996 05/31/1996
04/21/1996 05/15/1996 05/17/1996 05/31/1996
04/25/1996 05/25/1996 05/31/1996 05/31/1996
04/30/1996 05/22/1996 05/24/1996 05/31/1996
5 record(s) selected.
|
|
09-23-09, 07:19
|
|
Registered User
|
|
Join Date: Jul 2006
Location: Pune , India
Posts: 433
|
|
Quote:
|
Originally Posted by yvanroy
,NEXT_DAY(dot, 'Friday') as "Next Friday"
,LAST_DAY(dot) as "Last date of month"
|
Is this on 9.7 LUW ?
__________________
Rahul Singh
Certified DB2 9 DBA / Application Developer
|
|
09-23-09, 08:00
|
|
Registered User
|
|
Join Date: Jun 2002
Posts: 12
|
|
Yes, it is on LUW 9.7 only.
|
|
09-23-09, 08:42
|
|
Registered User
|
|
Join Date: Jun 2002
Posts: 12
|
|
Let me clarify my previous statement. The 2 functions works in LUW starting at version 9.7 both they are also available on z/os Version 8.
|
|
09-24-09, 04:17
|
|
Registered User
|
|
Join Date: Sep 2009
Posts: 2
|
|
On inserting the query
,NEXT_DAY(dot, 'Friday') as "Next Friday"
,LAST_DAY(dot) as "Last date of month" the message shows as folows:-
SQL0440N No authorized routine named "NEXT_DAY" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884
|
|
09-24-09, 07:17
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Please see yvanroy's second and third answers.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
date functions help
