Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Question Unanswered: selecting columns in date format (MM/DD/YYYY or YYYY-MM-DD)

    i've just started using DB2 and have not found IBM's help information all that helpful regarding my query. what i'd like to do is select a set of records based off a given beginDate and endDate range that is in MM/DD/YYYY format. i don't have any interest in changing the inputs to match the DB2 date format (it's storing the values in a timestamp value = '2004-04-28 15:14:59.323000') i'd like to know how to effectively use the DATE operator as i've seen some information indicating that it can be used for such things. unfortunately, there's no indication of the syntax that's required and the errors i'm getting from db2 aren't very helpful.

    my inputs are:
    USERID = 921265660866393984317036000000
    STARTDT (beginDate) = 04/28/2004
    STARTDT (endDate) = 04/29/2004

    with Oracle, the following works:
    select IDENTIFIER from BCHISTSESSN where USERID =
    AND (STARTDT >= TO_DATE(? 'MM/DD/YYYY')
    AND STARTDT <= TO_DATE(?, 'MM/DD/YYYY'));

    with SQL Server, the following works:
    select IDENTIFIER from BCHISTSESSN
    where USERID =
    AND (STARTDT >= ? AND STARTDT <= ?)

    here's a DB2 query that works. i'm interesting in using just a MM/DD/YYYY or even a YYYY-MM-DD format:

    select IDENTIFIER
    from BCHISTSESSN
    where USERID = 921265660866393984317036000000
    and STARTDT >= '2004-04-28 15:14:59.323000'
    and STARTDT <= '2004-04-29 15:14:59.323000'

    any help here is greatly appreciated.

    regards,
    jason

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow of course date works

    Hey cheer up!

    DB2 is a wonderful tool - you just have to
    get to know it a little :-)

    The DB2 function date(<myvariable>) extracts the date
    from a number of supported date formats, among those the
    US standard (which sucks), and the ISO standard (ISO Rules!!),
    as you have specified.

    Thus, your predicate should work using >'yyyy-mm-dd' or >'mm/dd/yyyy'

    If you have the date variable as a DB2 date, simple use: > '<yourdatevariable>'

    It is not necessary to use a fulle timestamp, DB2 Supports
    an ordinary date variable as used in the above example.

    There is plenty of documentation on DB2 and datetime variables, see
    the ftopmost 2 posts in this forum.

    BOW
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    This is a small write up about date format in CLP ...

    You can apply this theory for any of your applications


    The format of the date display of CLP depends on the locale settings of the client ...

    Is the locale is en_GB, the date format in CLP will be 'dd/mm/yyyy' (Us format is mm/dd/yyyy)

    If this format is not acceptable, on your Windows systems,
    In 'Settings'
    In 'Regional Options'
    On the 'General Tab'
    Under 'Language settings for the system'
    Click on 'set default'
    Then select the 'English(United Kingdom)'

    This works on W2K ... If you are having a differnt OS, please see your Windows Administrator ... also,the 'Language Settings for the System' is different from 'Your Locale'

    On Unix Systems,
    export LOCALE_ALL=en_GB
    ksh



    Whatever your locale is set as, if the input query refers to the date in the ISO format, it should work without any problems ...

    If the date format for all clients (irrespective of the client locale) is required in a specific format, bind the CLP packages using the DATETIME Option when binding. The bind should happen from every OS-db2level combination possible in the environment
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Sep 2003
    Posts
    6

    Smile feeling enlightened

    i found something about the CAST operator and now this query returns just what i need (i can still use the common 'MM/DD/YYYY' format):

    select IDENTIFIER from BCHISTSESSN
    where USERID = ?
    and (DATE(STARTDT) >= CAST(? AS DATE)
    and DATE(STARTDT) <= CAST(? AS DATE))

    thanks for the heads up about the locale settings. i see internationalization coming our way so this is good information to know.

    regards,
    jason

Posting Permissions

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