|
View previous topic :: View next topic
|
| Author |
Message |
ksk
Active User
.jpg)
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
Hi,
We have a reuqirement to compare date format YYYY-MM with the
YYYY-MM of a given date (given date will be in YYYY-MM-DD format).
I can do reference modification and extract Year and Month thru COBOL-DB2 program and can use it in SQL query. But is there any function to extract both YEAR and MONTH from a particular date in SQL query like YEAR for year part, MONTH for month part?
E.g,
BOOK_DT = '2008-11-01' and YEAR-MO = '2008-11'. I want to select Account number if YEAR-MO is equal to Year and Month part of BOOK_DT. I want to to this thru SQL query. |
|
| Back to top |
|
 |
trilokdewangan
New User
Joined: 19 Jun 2008
Posts: 39
Location: USA
|
|
|
|
Hi KSK
you can use SUBSTR function. Syntax is SUBSTR(variable name, starting position, length)
Example: BOOK_DT = '2008-11-01'
SUBSTR(BOOK_DT,1,7) = 2008-11 |
|
| Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
Trilok,
Did you execute your solution? I tried in SPUFI before posting this question, it's not working for me. |
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
Hi
Please try the following
| Code: |
SELECT ACCT_NO
FROM TABLE
WHERE YEAR(BOOK_DT) = YEAR(YEAR-MO)
AND MONTH(BOOK_DT) = MONTH(YEAR-MO)
|
|
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hi,
Given a date, time, or timestamp, you can extract (where applicable) the year, month, day, hour, minutes, seconds, and microseconds portions independently using the appropriate function:
| Code: |
YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp) |
You can replace "current timestamp" with the column name of intrest from your table. Here is a related link.
www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
| Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
Anuj,
I have tried with EXTARCT function before posting this question but it's not working for me. And tutorial is showing DB 9.1 version and probably mine is older version. |
|
| Back to top |
|
|
tarun_bhardwaj
New User
.jpg)
Joined: 18 Jul 2003
Posts: 39
Location: delhi
|
|
|
|
KSK,
The following query should work for you. I just tried it in SPUFI
| Code: |
SELECT CONCAT(CONCAT(SUBSTR(CHAR(YEAR(BOOK_DT)),1,4),'-')
,SUBSTR(CHAR(MONTH(BOOK_DT)),1,2))
FROM BOOK_TABLE; |
|
|
| Back to top |
|
|
|
|
