mod timestamp in SQL

[former_member205400]

Experts,

There is a SAP table I'm reading - RSPCPROCESSLOG and it has a timestamp called starttimestamp.

It liiks like: 20,110,312,222,659.8371270 and has a format of CCYYMMDDHRMMSS

If I am running a sql statement on it using se38 RSDU_EXEC_SQL is there a format that you suggest for getting an output like:

2011/03/12 22:26

Thanks,

Mike

[audunlea_hansen · Accepted solution]

Check what value your session-parameter nls_date_format has.

Oracle's date column counts seconds since 1/1-1970 -> seems like you output that value.

[lbreddemann · Accepted solution]

Sorry, but:

- you don't have a clue on this table

AND

- you have a terrible approach to use Oracle databases.

Your hint is obvious nonsense and misleading!

[audunlea_hansen · Accepted solution]

It's ok my hint was wrong and you corrected it

Dut I dislike the way You wrote it!!

Audun

[former_member205400 · Accepted solution]

Audun, thanks for your response anyway.

Lars,

yes, I knew the format.

I got a short dump adding to_date. Here is my sql:

select log_id, to_date(starttimestamp,'YYY-MM-DD') as my_start

from RSPCPROCESSLOG

Doesn't think it like native SQL. I get a CX_SY_NATIVE_SQL_ERROR exception:

<snip>

15 ULINE.

16 EXEC SQL.

17 OPEN C1 FOR

18 SELECT

19 "LOG_ID"

20 , "MY_START"

21 FROM

22 "/BI0/0300000124"

23 WHERE ROWNUM < 1000

24 ENDEXEC.

25 WHILE L_SUBRC = 0.

26 EXEC SQL.

>>>>> FETCH NEXT C1 INTO :L_S_TAB

28 ENDEXEC.

29 L_SUBRC = SY-SUBRC.

30 IF L_SUBRC = 0.

31 WRITE: /

32 L_S_TAB-LOG_ID UNDER 'LOG_ID'

33 ,L_S_TAB-MY_START UNDER 'MY_START

<snip>

Mike

35 ELSE.

36 EXIT.

37 ENDIF.

38 ENDWHILE.

39 EXEC SQL. CLOSE C1 ENDEXEC.

[lbreddemann · Accepted solution]

>

> Audun, thanks for your response anyway.

>

> Lars,

>

> yes, I knew the format.

>

> I got a short dump adding to_date. Here is my sql:

>

> select log_id, to_date(starttimestamp,'YYY-MM-DD') as my_start

> from RSPCPROCESSLOG

>

> Doesn't think it like native SQL. I get a CX_SY_NATIVE_SQL_ERROR exception:

Hmm... without the ORA-error code, we don't know what error you got...

But based on the way you used to_date, I guess, it's about the specified INPUT format...

Keep in mind: to_date casts to a data data type (which doesn't have a date format in itself). For that you provide the input pattern.

If you want to force a specific output pattern, you've to use to_char () on the date data type.

Basically you'll be using to_char ( to_date( xyz, '<INPUT PATTERN>'), '<OUTPUT PATTERN') to get a string version of the date information.

So, I'd check which ORA-error you get and check the format specification (what I called 'Pattern' above).

regards,

Lars

[former_member205400 · Accepted solution]

That SQL above I put in results in:

How to correct the error

Database error text........: "ORA-01861: literal does not match format string"

Database error code........: 1861

Triggering SQL statement...: "FETCH NEXT "

Internal call code.........: "[DBDS/NEW DSQL]"

Its not a normal timestamp in the true Oracle sence, its put into a dec 16 field.

Mike

[lbreddemann · Accepted solution]

Hi Mike,

hmmm.. I hoped my hints would be good enough...

It's not really that difficult.

As I wrote, you've to be careful about what you use as INPUT and what as OUTPUT of the conversion functions.

Let's look at this example:

select starttimestamp, 
        to_char(
           to_date (
                    trunc(starttimestamp),
                    'YYYYMMDDHH24MISS'),
        'DD-MM-YYYY HH24:MI:SS') out
from  RSPCPROCESSLOG 

Try to read it from inner-most function to the out-most function.

At first I make life simple by cutting away everything after the decimal point ( TRUNC ).

(Be aware that you've to care about this data, if you need it!)

Then I convert this number to a date data type and provide the parsing pattern (the date format):

to_date ( ... , 'YYYYMMDDHH24MISS')

Now I have a real Oracle date column which I now can cast to a character data type with a format I choose:

to_char ( ... , 'DD-MM-YYYY HH24:MI:SS')

The result looks like this:

STARTTIMESTAMP        OUT
-------------------- -------------------
20070720024321.630874 20-07-2007 02:43:21
20070720024554.484343 20-07-2007 02:45:54
20070720024824.608832 20-07-2007 02:48:24
20070721024128.77627  21-07-2007 02:41:28
20070721024231.955964 21-07-2007 02:42:31
20070721024335.122779 21-07-2007 02:43:35
20070721024452.64654  21-07-2007 02:44:52
20070723024136.178498 23-07-2007 02:41:36
20070723024306.355225 23-07-2007 02:43:06
20070723024429.745965 23-07-2007 02:44:29

Not too difficult, isn't it?

regards,

Lars

[former_member205400 · Accepted solution]

Lars,

That fixed it.

the TRUNC() function fixed it really.

Thanks for all your help.

Mike

[former_member205400 · Accepted solution]

Lars,

Just a follow-up.

IThat SQL works fine, but what I'm seeing in the log is

LOG ID 4LK5BK254CXJCKIYA597WX7F9

EVENT START RSPROCESS

EVENTP START 4I48Z3N551KUDTWTX6T2MKJWI

JOB COUNT 08325800

BATCHDATE 04/20/2011

BATCHTIME 08:32:58

STARTTIMESTAMP 20,110,420,133,259.9072570

ENDTIMESTAMP 20,110,420,170,538.2673400

So I see this started at 08:32 this morning, but the timestamp says 20,110,420,133,259.9072570 which is

2011-04-20 13:32:59

The question this is how does 13:32 align with the 08:32 time?

Mike

[lbreddemann · Accepted solution]

Timezone-shift.

The timestamp you're selecting is UTC - the one from the log your local time.

regards,

Lars

[former_member205400 · Accepted solution]

Lars, so if I subtract:

ENDTIMESTAMP 20,110,420,170,538.2673400

from

STARTTIMESTAMP 20,110,420,133,259.9072570

( 20110420170538.26734 - 20110420133259.907257 ) = 37278.360083 sec or 10 hrs

So I'm guessing I have to convert to time first before substracting these.

The actual time is like 4 hrs, not 10.

MIke

[lbreddemann · Accepted solution]

Mike, why do you think you can simply substract two timestamps?

These aren't "seconds-from-1-1-1970" or something like that!

It's YEAR-MONTH-DAY-HOUR-MINUTE-SECOND...

Your sustraction is just not defined that way on a timestamp like this!

regards,

Lars

[former_member205400 · Accepted solution]

Lars, Do you have a suggestion? Mike

[lbreddemann · Accepted solution]

>

> Lars, Do you have a suggestion? Mike

Sure - make yourself familar with date arithmetics

[look e.g. here|http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#SQLRF00208]

Seriously, this is not completely trivial and if I would be in your place I'd look around for some ABAP function modules that provide me the functionality you seem to want before I start programming this on my own.

Just my two cents on this!

Lars

[lbreddemann]

HI there,

please ignore the nonsense about the NLS-stuff from the other post!

Mike, why do you want to use this report to access this table?

As SE11 easily tells you, the column is the

UTC time stamp in long form (YYYYMMDDhhmmss,mmmuuun)

and it is stored in DEC format (that is NUMBER on DB-level).

If you want to perform selections on this table, why not use SE16?

If you want to report from this (internal) table, why not write ABAP?

The ...execute_SQL report really is not meant for such things.

Anyhow, in order to get the date in a usable way, you would need to convert it into a date format ... the TO_DATE function is your friend here. Just check the Oracle documentation how to use it.

Regards,

Lars