Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Posts
    63

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

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Apr 2007
    Posts
    63
    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

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2007
    Posts
    63
    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?

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    @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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Apr 2007
    Posts
    63
    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
    Last edited by winniewang; 04-11-07 at 22:29.

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Apr 2007
    Posts
    63
    the result like this: 7 /01/2001
    Last edited by winniewang; 04-12-07 at 08:14.

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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 ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Apr 2007
    Posts
    63
    hahaha...
    You are right.Thanks a lot^_^

    Thanks
    Winnie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •