cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with Oracle Date - Format

Former Member
0 Kudos

Hi:

At my DB all DATE fields have this value "2006-01-02 00:00:00.0" but no matter what format I try always display "01 Jan 2000" ( DSTR(@END_DATE,'DD MON YYYY') or TSTR(@END_DATE,'DD MON YYYY')). It seems that ignore what's left from 00:00:00.0 and take in count only the hours, minutes and seconds

I read: "Visual composer Expressions Syntax.doc" and SAP Visual Composer Documentation but I can't make it work.

Thanks a lot for your time on this thread.

Rocío.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Rocio,

this is an interessting information. I had a similar problem with an ABAP RFC. The date from a variable of the data type date returns also the 01 Jan 2000, but when I get the data from a table of the backendsystem, which contains the same value also in a date field I get the correct value. A colleague writes an OSS message. If I get more information I'll give you more information.

Best Regards,

Marcel

Former Member
0 Kudos

Thanks Marcel for your time.

Please keep in touch.

Best Regards,

Rocí

Former Member
0 Kudos

Hi Marcel:

Did you solve this problem?.

Thanks for your time.

Rocío.

Former Member
0 Kudos

Hi Rocio,

this is a long story. I opened a OSS message and the SAP support told me that Visual Composer doesn't interpret the data types correct. There are similar issues with all country specific formats in date, numbers and so on. I get the data from the SAP BI and that's why I thought this must be a bug of SAP in VC or BW, but the support told me that this is no bug. If I want that VC interpret the data type correctly then I have to fill out a developer request. Anyway this is a known bug with the country specific values. I use a workaround. I use a field with the data type text instead of date (this make no sense, but it works).

I hope these bugs will be fixed in the upcoming SPS of NW04s.

Hope that helps,

Best Regards,

Marcel

Former Member
0 Kudos

hi, you can use VC system Date type.right-click control properties, click formatting, this list all VC support date format. if this date format cann't right you want date format, you can use expression edit formual.

hope help you.

Former Member
0 Kudos

Hi Jun:

I use "expression edit formula" and no customs format before posting but I always get the same: the format is apply but the day, month and year is getting wrong.

Thanks for your time.

Rocío.

Former Member
0 Kudos

Just a guess: have you tried to use both the time and the dat function and concatenate them as string?

Former Member
0 Kudos

Hi Mario!:

I don't know if I'm getting you right: I try with DATE and TIME function in different tests but I didn't mix them. At individual tests ("2006-01-02 00:00:00.0") when I ask for the year part always returns 0 and not 2006....no matter if I use Date or time functions. If you think I'm witting wrong the formula please copy it in your next post.

Thanks a lot for your time.

Rocío.

Former Member
0 Kudos

If you are using selects within VC to get the dates from your Oracle db here is a method I'm using.

Write your select statement as a formula in the @SQL_STATEMENT field on the line connecting your Oracle DB data service and either your start point or your input form. Within this select statement use to_char conversion:

"select to_char(startdate, 'dd/mm-yy') as startdate from table order by startdate desc" (remember the "" around the statement!)

Only problem with this method is that dates no longer sort as dates when clicking the column header in the flex table at runtime, but rather as text.

Hope it helps

Henning

Former Member
0 Kudos

Rocío

I experienced similar challenges with the Oracle DATE format (I did access the (external) Oracle DB via a JDBC defined system).

As a workaround, I used the PL/SQL "TO_CHAR" function to convert the Oracle DATE in three strings: day, month, year. In VC, I then used the expression "date(@day,@month,@year)" to get a date format in VC from the three stings.

Would like to know as well, how an Oracle DATE field could be accessed in VC directly without aboove workaround.

Regards

Adrian