Results 1 to 6 of 6

Thread: Exporting dates

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

    Question Unanswered: Exporting dates

    AIX 5.2 DB2 8.1.4

    Hi guys

    I'm trying to export database tables to positional files
    which works well with alle character and numerical values
    But any attempt to concatenate dates results in error:

    --x--

    EXPORT TO "u:\mdr.txt" OF DEL MESSAGES "u:\mdr.log"

    SELECT (A.d_anmdato)||(a.d_koncdato)

    FROM mdr.t_aflv04 A

    --x--

    The select by itself reports:


    com.ibm.db.DataException: Der er
    opstået en fejl i databasesystemet. : [IBM][CLI
    Driver][DB2/6000] SQL0440N No authorized routine named "||" of
    type "FUNCTION" having compatible arguments was found.
    SQLSTATE=42884


    Does anyone know a workaround?

    Cheers
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    char(date1)||char(date2)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

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

    Arrow Iso

    Thanks Sathyaram

    The only problem with this approach is that it superseeds
    the default settings on the export utility and returns the
    dates in dd-mm-yyyy instead of ISO which I require.

    Do I really have to cut the date to pieces and string it together?

    Cheers
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you tried using modified by datesiso ..

    If this does not work with char(date1) then you can try using CTE (Not tested, but you can give a try)

    export to file1.del of del
    modified by datesiso
    with temp as
    (
    select date1,date2 from table1
    )
    select char(date1)||char(date2) from temp
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you RTFM to look up the definition of the CHAR function you'll see that it allows you to specify date string format, something like this:
    Code:
    char(date1, ISO)

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

    Thumbs up Dateiso

    Hey n_i

    I did just that and it worked, though i still had to cut
    out the '-''s afterwards - Thanks both.

    Cheers
    Last edited by Tank; 06-20-04 at 16:04. Reason: Found solution
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

Posting Permissions

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