Remove last 10 numbers from values

[#1]

Hello,

A simple question from a Qlikview personal edition user. I'm using an Oracle database with a numeric column which looks like the follow:

TVRZ
201210050700120000
201210050700130000
201210090700150000
201210090700140000
201210090700160000
201210100700190000
201210100700170000
201210100700180000
201210110700140000

These numbers are supposed to be dates, the first 8 digits represents a date. I dont know why the values are 18 digits long, it is still to long if it is including the hh:mm:ss.

I still want to load it as a date so i can do some calulations with it. Does somebody know how i can load the column without the last 10 digits and load it as a normal date value?

[rbecher]

Why not just use:

Date(Timestamp#(TVRZ,'YYYYMMDDhhmmssffff')) as YourDate

..it's pretty straight forward.

-  Ralf

[#3]

The response from Ralf Becher should work (using timestamp# function). If it is not working could be because the data type in the database does not convert to text in the expected way, or the default format for the Date field in Document Properties is not assigned to a date type.

Could you attach a document with only this field extracted?

[#4]

Thanks for all the help but the result is always an empty column. I indeed think Juan is right when mentioning the not converting the data in the expected way. I did some checks, and if i load the data in excel it looks like this:

TVRZ

2,01212E+17

2,0121E+17

2,0121E+17

2,01211E+17

2,01211E+17

2,0121E+17

2,0121E+17

It looks like the Comma needs to be removed first or something. Any thoughts?

@Juan, i added an QVW file as suggested

[rbecher · Accepted solution]

This seems to work, but I don't know why this must be so complicated (see listbox expression, should work also in script):

=Date(Date#(text(floor(TVRZ/10000000000)),'YYYYMMDD'))

- Ralf

[#6]

Its working now, thanks!