Newbie question: Split a date-time field into date while preserving the format

[datanibbler]

Hi,

I have a field containing a date and time in the format >dd.mm.yyyy hh:mm:ss<. What I want to do is split out only the date for selection purposes while preserving the DATE format - I want to add a day-month-quarter mapping to the diagram and possibly link the table where that field is to others containing a date - that is why I absolutely have to keep it as a DATE field, not as a STRING - as such, I know how I could do it...

Can anybody advise me on this, please?

Thanks a lot!

Regards,

DataNibbler

[Gysbert_Wassenaar]

floor(MyTimestamp) will remove the time part of a timestamp (aka datetime), leaving only the date part

frac(MyTimestamp) will remove the date part of a timestamp, leaving only the time part

If the timestamp field isn't a numeric field, but a string you can use string manipulation functions. For example

date#(subfield(MyDateString,' ',1),'dd.MM.yyyy') as MyDate

date#(subfield(MyDateString,' ',2),'hh:mm:ss') as MyTime

Replace MyTimestamp and/or MyDateString with your fieldname and make sure you use capital MM's to specify months and use mm for minutes.

[datanibbler]

Hi,

in principle, that works - that was the first thing I tried, actually, but I thought I was doing something wrong: Now I do get a date in proper format dd.mm.yyyy - but I also get some number, I don't know what that is.

I cannot use that as a dimension in my diagram because for some reason, there only that number is displayed (when using the same command), no date.

Also, when I look at the table preview, the date-time-stamp looks very weird - it is stored there as a decimal value. Is that correct?

We have a multi-tier architecture here: There is one qvw loading data from the database once in the morning and stores it all in QVD files. My document uses those QVD files. Maybe I have to change the script loading the data in the first place?

Thanks for the help!

Best regards,

DataNibbler

P.S.: It is even worse - I just realized I had an inconsistency: In the object_properties dialog, there are two places where one can put a formula and I had two different ones - one was a LEFT, splitting out the date all right, but changing it into a STRING. The other was the FLOOR command. I deleted my own LEFT function to test the result of the FLOOR command - now I have no date whatsoever, but only a number. That must be because of the format of the base data...

[Gysbert_Wassenaar · Accepted solution]

now I have no date whatsoever, but only a number. That must be because of the format of the base data...

Very likely. You can add a format in the script with the date() function: date(floor(MyTimestamp),'DD.MM.YYYY'). Or you can set the formatting on the Number tab of the properties screen of the object where you use the field.

[datanibbler]

Hi,

thanks for the answer! - While this was in the moderating process, I have found the solution: I read up daily and I learnt that the datetime stamp is internally saved as a numeric value and that the FLOOR function simply rounds it down, so it is the correct function of course. I just have to make sure it is displayed as a date, either by the DATE function or in the properties of the diagram. I'm happy to notice that, though giving me the correct solution, you are not now telling me anything I have not thought of since yesterday.

Thanks a lot!

Best regards,

DataNibbler