Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    18

    Unanswered: Simple DateTime Query question

    Hi,
    I am trying to access a table which has a tgame_datetime field with data_type 'DECIMAL'.The values in this field are long values like '1,102,148,345,000' ,'1,102,148,346,000' ,'1,102,148,347,000' etc.
    I can access them and format them later on,but what I need is that I should be able to run a query depening upon specific dates.So,if I try this or formatted dates(YYYY/MM/DD HH:MMS):

    select gr.game_id,gr.game_datetime from game_results as gr,game as g where gr.tournament_id=g.tournament_id and gr.tournament_datetime between '1,102,148,345,000' and ''1,102,248,345,000'

    It throws this error:
    javax.servlet.ServletException: DB2 SQL error: SQLCODE: -10, SQLSTATE: 42603, SQLERRMC: '

    Is there a way to perform formatting on the query itself.Please tell me how to solve this.Thanking you in Advance.

  2. #2
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    Not sure as to what you're asking

    But the error message means:-

    SQLSTATE 42603: An unterminated string constant has been detected.

    Your query has a double quote " rather than a single quote ' in it, if you look closely
    jdey@macehill.co.uk
    http://www.macehill.co.uk

  3. #3
    Join Date
    Jan 2005
    Posts
    18

    sorry..that was a typo

    that was just a typo my friend,it doesn't works with single quotes either and throws a error like this:
    javax.servlet.ServletException: DB2 SQL error: SQLCODE: -401, SQLSTATE: 42818, SQLERRMC: BETWEEN

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jasoberai
    Hi,
    I am trying to access a table which has a tgame_datetime field with data_type 'DECIMAL'.

    ...

    select gr.game_id,gr.game_datetime from game_results as gr,game as g where gr.tournament_id=g.tournament_id and gr.tournament_datetime between '1,102,148,345,000' and ''1,102,248,345,000'
    '1,102,148,345,000' doesn't look like a DECIMAL to me... more like a string

  5. #5
    Join Date
    Jan 2005
    Posts
    18
    Can someone just tell me how to access a date field that has large values,can we specify some formatting on the query itself for eg.
    format(gr.tournament_datetime) type of thing.

  6. #6
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    The thing is

    We're not really understanding the question.

    The last error you were getting is:-

    SQLSTATE 42818: The operands of an operator or function are not compatible.

    Which as the previous guy says is because you're trying to compare a column which you've apparantly defined as a decimal with what looks like a string.

    DB2 has a timestamp datatype whose format is 'yyyy-mm-dd-hh.mm.ss.nnnnnn' and a date datatype whose format is 'yyyy-mm-dd'

    Presumably the decimal value in your tournament_datetime field is the number of microseconds from a particular date?
    If this is so then

    timestamp_iso(<your seed date>) + <your decimal value> microseconds would return a result
    e.g. timestamp_iso('1900-01-01') + 1102148345000 microseconds

    If you just want to modify your query so that the string comparison with a decimal is valid, then just remove the commas i.e. '1102148345000' rather than '1,102,148,345,000'
    jdey@macehill.co.uk
    http://www.macehill.co.uk

Posting Permissions

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