Date format for query string

FLANDERS · #1 (**permalink**) 09-14-10, 05:34

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

ARWinner · #2 (**permalink**) 09-14-10, 08:16

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

FLANDERS · #3 (**permalink**) 09-14-10, 08:46

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

Internet Services

Web Development

Digital Marketing

Consumer Tech