Who can help me to translate SQL Server query to DB2?

winniewang · #1 (**permalink**) 04-10-07, 06:18

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), MonthNumberOfYear) + '/ ' + '1/' + CalendarYear AS DATETIME)

I'm a new one to write query for DB2, I tried to used TIMESTAMP_FORMAT, but it was in vain.

Any reponse is appreciated^_^

Thanks!
Winnie

ARWinner · #2 (**permalink**) 04-10-07, 08:14

It looks like you are trying to return the 1st of a particular month.

date(char(MonthNumberOfYear)||'/01/'||CalendarYear)

This is assuming that CalendarYear is a string type (char, varchar).

Andy

stolze · #3 (**permalink**) 04-10-07, 15:36

If you have the date value already as DATE:

Code:

myDate - ( DAYS(myDate) - 1 )DAYS

Code:

$ db2 "values current_date - ( DAY(current_date) - 1 )DAYS"

1
----------
04/01/2007

  1 record(s) selected.

winniewang · #4 (**permalink**) 04-10-07, 22:05

Hi ARWinner,

Why I always encouter 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(char("MonthNumberOfYear")||'/01/'||Char("CalendarYear"))

Thanks^_^
Winnie

Wim · #5 (**permalink**) 04-11-07, 06:42

Probably the CHAR function didn't return a clean result.

Do a

Code:

SELECT char("MonthNumberOfYear"), Char("CalendarYear") FROM ...

and see what the results are.

If the year is something like "2007.", get rid of the dot ".".

I also believe DB2 expects 2 digits for the month, so you must get "01" for the month "1" won't do.

stolze · #6 (**permalink**) 04-11-07, 06:47

Quote:

Originally Posted by winniewang

Why I always encouter 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(char("MonthNumberOfYear")||'/01/'||Char("CalendarYear"))

Thanks^_^
Winnie

First, a double-quote is not valid where you are using it. Next, CHAR('MonthNumberOfYear') is not a valid component in a date value - it is not a number as it should be but rather a string. What DB2 wants to convert to a date is this string

MonthNumberOfYear/01/CalendarYear

Which is, of course, an invalid datetime expression. So you should tell us where the numerical values for MonthNumberOfYear and CalendarYear come from.

winniewang · #7 (**permalink**) 04-11-07, 07:29

And I’m sure “char(MonthNumberOfYear AS CHAR(2))” is correct. If I make it as a column. And if I change it into: date('12'||'/01/'||CalendarYear), it is OK. The MonthNumberOfYear's datatype is int, CalendarYear's datatype is string. Could you tell me why?

ARWinner · #8 (**permalink**) 04-11-07, 07:59

It is not working because MonthNumberOfYear is less than 10 and the date string you are creating is like '4/11/2007' where it needs to be like '04/11/2007'.
so you need to detect this situation and adjust the query accordingly.

date(case when MonthNumber_of_Year < 10 then '0' else '' end || char(MonthNumberOfYear) || '/01/' || CalendarYear)

Andy

stolze · #9 (**permalink**) 04-11-07, 13:16

@ARWinner: No, this is unnecessary. DB2 does not require the leading 0s.

Code:

$ db2 "values date('4/3/2007')"

1
----------
04/03/2007

  1 record(s) selected.

@winniewang: "CHAR(MonthNumberOfYear AS CHAR(2))" is not correct. It would have to be a CAST. You should really have a look at the string expression itself. What does the following give you?

Code:

$ db2 "values 'X' || CAST(MonthNumberOfYear AS CHAR(2) || '/01/' || CalendarYear) || 'X'"

Please show us the result of this.

Note: A simple CHAR(<int-value>) will convert the integer to a CHAR(8) with padding whitespaces to the right. In that case, RTRIM(CHAR(<int-value>)) would be required to get rid of those whitespaces.

winniewang · #10 (**permalink**) 04-11-07, 21:17

Hi Stolze,
You are right.But it still return error like this: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(MonthNumberOfYear AS char(2))||'/01/'||CalendarYear)

It seems that the the result of "Cast(MonthNumberOfYear AS char(2))" can not use as the date's parameter...

Thanks^_^
Winnie

stolze · #11 (**permalink**) 04-12-07, 06:13

Please answer my previous post and show us the result of the following SQL statement:

Code:

$ db2 "SELECT Cast(MonthNumberOfYear AS char(2)) || '/01/' || CalendarYear FROM ..."

Right now we can only give wild guesses...

winniewang · #12 (**permalink**) 04-12-07, 07:10

the result like this: 7 /01/2001

stolze · #13 (**permalink**) 04-12-07, 14:29

There is a space after the '7', which is not allowed for datetime expressions. This space originates from your CAST to a CHAR(2). So you will have to trim the string before the concatenation:

Code:

SELECT RTRIM(CHAR(MonthNumberOfYear)) || '/01/' || CalendarYear FROM ...

winniewang · #14 (**permalink**) 04-12-07, 21:13

hahaha...
You are right.Thanks a lot^_^

Thanks
Winnie

Internet Services

Web Development

Digital Marketing

Consumer Tech