To extract month and year from a column in the table

[Anusha Pesala]

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.

[r035198x]

Read http://www.ibm.com/developerworks/da.../0211yip3.html

Try some SQL and post if you get problems with your SQL.

[Anusha Pesala]

I have used Extract function and it is throwing error.
Error: [SQL0171] Argument 1 of function EXTRACT not valid.
SQLState: 42815
ErrorCode: -171

[r035198x]

What code did you try?

[Anusha Pesala]

SELECT EXTRACT(MONTH FROM mschof) 
     FROM ccdata.rcmast;

[r035198x]

That's not valid syntax, you probably want

SELECT MONTH(mschof) FROM ccdata.rcmast;

[Anusha Pesala]

when i use the above query still i am getting same error

[r035198x]

What type is the mschof column?

[Anusha Pesala]

the type of the column is date type

[vijay2082]

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(curre nt date) as char(4)) ||','|| cast(month(curr ent date) as char) from sysibm.sysdummy 1 with ur"
2013,4

C:\>db2 "select cast(year(curre nt date) as char(4)) ||','|| cast(month(curr ent date) as char) from sysibm.sysdummy 1 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>.<ta bname>"

Cheers, Vijay