date format

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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