469,315 Members | 1,802 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

To extract month and year from a column in the table

I have a table which has date as (yyyy-mm-dd)'2000-02-04' ,i want to get only month and year.
i.e the expected result should display 2000,02.
cqan you help me how to get.
Apr 8 '13 #1
9 29988
r035198x
13,262 8TB
Read http://www.ibm.com/developerworks/da.../0211yip3.html

Try some SQL and post if you get problems with your SQL.
Apr 8 '13 #2
I have used Extract function and it is throwing error.
Error: [SQL0171] Argument 1 of function EXTRACT not valid.
SQLState: 42815
ErrorCode: -171
Apr 8 '13 #3
r035198x
13,262 8TB
What code did you try?
Apr 8 '13 #4
Expand|Select|Wrap|Line Numbers
  1. SELECT EXTRACT(MONTH FROM mschof) 
  2.      FROM ccdata.rcmast;
Apr 8 '13 #5
r035198x
13,262 8TB
That's not valid syntax, you probably want
Expand|Select|Wrap|Line Numbers
  1. SELECT MONTH(mschof) FROM ccdata.rcmast; 
Apr 8 '13 #6
when i use the above query still i am getting same error
Apr 8 '13 #7
r035198x
13,262 8TB
What type is the mschof column?
Apr 8 '13 #8
the type of the column is date type
Apr 8 '13 #9
vijay2082
112 100+
Hi,

use below query and replace the current date with your column name which is a date data type.

C:\>db2 -x "select cast(year(current date) as char(4)) ||','|| cast(month(current date) as char) from sysibm.sysdummy1 with ur"
2013,4

C:\>db2 "select cast(year(current date) as char(4)) ||','|| cast(month(current date) as char) from sysibm.sysdummy1 with ur"

1
------
2013,4

1 record(s) selected.

If this doesn't work then paste the below output.

db2level

db2 connect to <db_name>

db2 "describe table <tabschema>.<tabname>"

Cheers, Vijay
Apr 16 '13 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Zero.NULL | last post: by
reply views Thread by =?Utf-8?B?bW9oYW5yYWpfaw==?= | last post: by
3 posts views Thread by =?Utf-8?B?R3V5IENvaGVu?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.