Compare TimeStamp with Date Attributes

technopak

Hi Folks,

I'm trying to compare a Date attribute of an object with the Date_Part of a TimeStamp attribute of another object and am a bit baffled. The problem seems to centre around the data types involved. A piece of my code goes like this -

FIND Event WHERE Date_Part(Event.StartTime)>=Enquiry.StartDate AND Date_Part(Event.StartTime)<=Enquiry.EndDate

.... where the Event is an object containing planned events, and Enquiry is an object containing dates to be checked.

The query checks for planned events within a specifed time period identified by the Enquiry object attributes.

However, my query returns nothing at all (even though there are events scheduled). I'm sure it has something to do with the comparison of data types but have tried various functions to no avail.

Can anyone suggest a way to compare TimeStamp with Date attribute types ?

tford

Have you tried adding a an attribute Event.StartTimeDate and calculating it with the rule: Event.StartTimeDate=Date_Part(Event.StartTime). That will allow you to look "under the hood"

Tom

technopak

Thanks for the reply Tom,

Only question is - what attribute type should I use for the suggested Event.StartTimeDate attribute ?

Won't that give me a similar data type problem or at least muddy the water a bit when I try to relate this to my real scenario ? If I set up the Event.StartTimeDate attribute as text for example, wouldn't the data type being calculated have to be text to be stored in the attribute ?

Regards,

Peter

tford

Peter,

Not sure what it will give you, but I would not think it would muddy the waters.

I would made it a Date type attribute, then depending on it's results I would try using it:

FIND Event WHERE Event.StartTimeDate>=Enquiry.StartDate AND Event.StartTimeDate<=Enquiry.EndDate

This is just a guess though -- not based on actual experience or testing.

Tom

technopak

Hi again,

I guess the real crux of my question is - what is the data type returned by the Date_Part function ?

If I knew that I would be able to use a suitable function to convert for comparison purposes. And I know - it's probably in the documentation somewhere 🙂

Peter

technopak

Tom,

From the reading I've done and some experimentation, the Date_Part function appears to return a text value.

Text comparisons are the only ones I could find references to in both documentation and forums.

Unfortunately my efforts to convert the Date_Part return value to Date type data and also to convert the Date type data to text using As_String have proved unsuccessful. And yes, I need to keep the Enquiry object attributes as dates for ease of selection using the datepicker by the user.

Must admit, I'm a bit lost 🙁

aware_support

DATE_PART definitely returns Date. Your initial query appears correct. If it doesn't work it could be a bug - we'll check it out.

In the meantime - why do you need to convert to dates, why don't you just compare timestamps?

technopak

Thanks for the post Support 🙂

Following Tom's suggestion, I created a test attribute in the Event object and yes the Date_Part did return a date value making my comparison easy. Phew !

The reason I want to use dates is to make it convenient for users to choose dates from a calendar date picker rather than possibly enter some non-standard date type - trying to standardise input for this particular query.

Full marks to Tom and Support !

Thanks for the help,

Peter

technopak

Final note to Support -

The only way I could get my query to work was using Tom's suggestion.

The direct approach using the query mentioned above won't work for me. But when I create a new attribute in the Event object of type Date to hold the DATE-PART of the StartTime TimeStamp attribute of the same Event object, I can then run a revised query to display the objects which satisfy the criteria.

Regards,

Peter

rocketman

Just thought I'd remind Support, As of V4.7 - build 1223, a query using DATE_PART still appears to be not working

FIND TLSale WHERE DATE_PART(TLSale.StartTime)=LoggedInMembers.Session_MWdate ORDER BY TLSale.StartTime

This query was intended to produce a list of people who had booked a trial lesson on a given day (trial lessons being scheduled to a particular hour of the flying day - hence the reason we use TIMESTAMP field