Timestamp to date with an Oracle database

Paul Sanders shared this question 2 years ago
Answered

I believe that to change a field data type from timestamp to a date, I need to create a Calculated Field at the View level in freehand SQL. Can anyone provide the SQL statement for an Oracle database please? I have tried several suggestions from Stack Overflow, but I get errors.

Thanks and regards

Paul

Comments (10)

photo
1

Hi Paul,

are you aware that you don't have use a Freehand SQL Calc Field? You can get Yellowfin to do it automatically by clicking the + button at the bottom of the Prepare screen, then selecting Date Function, then selecting Date as shown below:

/x6WUmsEuIvEAAAAASUVORK5CYII=

I've just tested this an Oracle data source and it is working correctly. Please let me know if you have any issues with it.

regards,

David

photo
1

Hi David

Yes, but it did not help with my underlying problem. What I am trying to do is create a calculated field by adding

the date a guest arrives (a date)+

the number of nights' stay (numeric)=

the date a guest departs (date).

After using the function you suggested and then trying to create the calculated field, I get "Invalid data types, TIMESTAMP and NUMERIC for arithmetic operation PLUS". The new date field is still deemed to be a timestamp.

Any suggestions?

Regards

Paul

photo
1

OK, I'll play around with it over here now and let you know what I find...

photo
1

this is definitely working for me:

/zzKn6aAAAH6P8DLUeZHZnoNlEAAAAASUVORK5CYII=

/EEGxb1hbkAAAAASUVORK5CYII=

Have you tried that?

regards,

David

photo
1

and then apply the Date format via Column Formatting:

/U+Bk7EaiAAAAAElFTkSuQmCC

/mcArBFmGxvQAIwG1gizjQ1oAEYDa4TZxgY0AKOBNcJsYwMagNHAGmG2sQENJBvtAABbMNvYgAawzwAAwB5gNACAPcBoAAB7gNEAAPYAowEA7KHyUY3pugEAQDYqfyqA0QAAKweMBgCwBxgNAGAPMBoAwB5gNACAPcBoAAB7gNEAAPYAowEA7AFGAwDYA4wGALAHGA0AYA8wGgDAHmA0AIA9wGgAAHuA0QAA9gCjAQDs4f8B8rVMdCEaMx0AAAAASUVORK5CYII=

photo
1

Hi David

Instead of '1' in cast(ipclass.startdate as date) + 1, what is the sql to add the 'no of nights' field? Something like

cast(ipclass.startdate as date) + (ipclass.numberofnights) ?

regards

Paul

photo
1

Or perhaps I just do the calculated field at the report level?

photo
1

That is still working for me, here is my new calc field at the view level:

/g4kYbRU09QgAAAABJRU5ErkJggg==

and here it is in a report (once again, with the Column Formatting changed from Timestamp to Date)

/H+8s7S13QjPYwAAAABJRU5ErkJggg==

photo
1

Hi Dave

Success! Worked a treat.

Thanks so much for the help. You can mark this one closed.

Regards

Paul

photo
1

That's great news Paul, thanks for letting me know!

regards,

David