Skip to Content
author's profile photo Former Member
Former Member

date format

Hi,

I'm running MaxDB 7.6.00 buid 12.

We have to pick up some data in this DB and to make it easier, i have to create a new view. In afct, i have to fild in a table (d_ptage and h_ptage) containing a date for the first one (YYYY:MM:DD 00:00:00:000000) and a time for the second one (HH:MM:SS). the result must be one field (DD/MM/YYYY HH:MM:SS)

CREATE VIEW ptage_infotel AS SELECT n_pers,badge,  concat(date_format(date(d_ptage),'%d/%m/%Y'),h_ptage) AS pointage FROM ptage

But the date_format function is "unknow"

Any help is welcome, i'm used to MySQL, but not MaxDB.

Regards,

Nico

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 29, 2008 at 01:59 PM

    Hallo,

    there is no such function available in the default SAP MaxDB. Only, if you are using the sqlmode Oracle (Oracle-compatibility-mode),

    then TO_CHAR is available. But using sqlmode Oracle may cause some trouble in the rest of your application.

    As you do not use one of the predefined datetimeformats (please see reference manual for this),

    a mixture of conversion-functions and string functions will help:

    REPLACE(CHAR(DATE(d_ptage),EUR),'.','/') || ' ' || CHAR(h_ptage, ISO)

    Regards,

    Elke

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 29, 2008 at 02:18 PM

    Hi,

    Thanks,it seems to make what i want, but still get an error "data type must be compatible"...

    I tried without the ",ISO" for the time format, but same issue.

    Nico

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 29, 2008 at 02:30 PM

    Hi,

    mhm, let's check, what may be different to my testcase.

    d_ptage is of datatype TIMESTAMP, h_ptage is of datatype TIME?

    please check

    DATE(d_ptage)

    and

    CHAR(DATE(d_ptage),EUR)

    and

    CHAR(h_ptage, ISO)

    each for its own. They do work?

    REPLACE(CHAR(DATE(d_ptage),EUR),'.','/') is ok, too?

    If this is not ok or the final concat causes the trouble, then there may be some problem

    with ASCII and Unicode.

    Which kernel-version do you use? How does your installation parameters _UNICODE and

    DEFAULTCODE look like?

    Does it help, if these 3 character-constants are surrounded by ASCII (...) each?

    Good luck,

    Elke

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 29, 2008 at 02:48 PM

    Hi,

    h_ptage was already a CHAR.

    create view ptage_infotel as select n_pers, badge, replace(char(date(d_ptage),EUR),'.','/') || ' ' || h_ptage

    completed successfully!

    Thanks

    Nico

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.