Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    37

    Unanswered: Date format for query string

    Hi,
    I'm creating a fairly simple query but it involves dates. I'm generating a temp calendar table from start date to end date, which are strings. I cannot know what region the query will be run e.g. USA/Europe etc. How do I know what way to format the string version of the date so it will always work?

    Code:
    WITH cte_months (aMonth)
    AS 
    ( 
        select date(TO_DATE('2009-11-01', 'YYYYMMDD')) from sysibm.sysdummy1 
        UNION ALL 
        select aMonth + 1 month from cte_months
        where aMonth + 1 month < date(TO_DATE('2010-11-01', 'YYYYMMDD') )
    ) select * from cte_months
    or

    Code:
    WITH cte_months (aMonth)
    AS 
    ( 
        SELECT date('2009-11-01')  aMonth from sysibm.sysdummy1      
        UNION ALL 
        select aMonth + 1 month from cte_months
        where aMonth + 1 month < date('2010-11-01')
    )
    Both versions work fine on my test database (in ireland), but i'm concerned that they will be issues if the same query is ran against a US based server? I think the second one is the best since its ISO format so DB2 should always understand that, independent of where the server is running - is this correct?

    thanks
    Last edited by FLANDERS; 09-14-10 at 07:08.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The output is determined by the client or the application using the data. I ran your second query (in the US) and it gave me nice looking output.

    Andy

  3. #3
    Join Date
    Nov 2009
    Posts
    37
    Thanks Andy, good to know!

    I'm not overly concerned about the format of the output, im concerned about the actual SELECT failing. I thought maybe it could fail due to using EUR format in US database or vice versa. However the yyyy-mm-dd format is the ISO format which according to DB2 reference will always work so i'll stick with that.

    Cheers

Posting Permissions

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