translate SQL Server query to DB2

[winniewang]

Hi everyone,

Who can help me to translate the SQL Server query to DB2 query?
The query were shown as below:
CAST( CONVERT( CHAR(2), MonthNumberOfYe ar) + '/ ' + '1/' + CalendarYear AS DATETIME)

Any reponse is appreciated^_^

Thanks!
Winnie

[Snib]

If I understand that SQL correctly it is returning the date for the first day of the current month in US date format (MM/DD/YYYY).

So, something like this should do the trick:

select strip(char(mont h(current date))) concat '/1/' concat
char(year(curre nt date))
from sysibm.sysdummy 1
;

So for this month it would give the following:

4/1/2007

Regards

Snib

[winniewang]

I want to convert char datetype to date datetype. But I aways receive the error:The syntax of the string representation of a datetime value is incorrect. SQLSTATE: 22007, SQLCODE: -180 (Microsoft DB2 OLE DB Provider)
My query is:
date(Cast(Month NumberOfYear AS char(2))||'/01/'||CalendarYear )
I'm sure the "Cast(MonthNumb erOfYear AS char(2))" is right. And if I change my query like this: date('12'||'/01/'||CalendarYear )
It seems that the the result of "Cast(MonthNumb erOfYear AS char(2))" can not use as the date's parameter...
Who can tell me what's wrong with my query?

Thanks
Winnie

[chandu031]

Hi,

The problem with the query is when you cast say month number 4 as char(2) it will return '4 ' .. So you have to trim this for spaces..


Use this query :

select date(rtrim(Cast (month(current date) AS char(2)))||'/01/'||'2007' ) from sysibm.sysdummy 1


Hope this is helpful..

[winniewang]

Thank you!
You are right.

Thanks
Winnie