Date calculations not staying in US format

ab042 · Post by **ab042** » Sun Aug 13, 2006 3:39 am

If appears that when doing Date calculations that they do not aways stay in a US format or the format selected for the Attribute.

For example: When doing date calculations I've had US dates (MM/dd/yy) as a format and if I add days or time, they show up in the Calculated field as dd/MM/yy

Also I just noticed I had a formula that used DATE_ADD(CURRENT_DATE,5) and the Log Viewer showed
Condition Evaluated to true: 08/12/06<(:12/08/2006,5):17/08/2006

It appears the logic was correct in the above case but the formatting was not.

When a formula is used to calculate an attribute, the attribute is showing an incorrectly formated date.
The attribute format and format prompt both showed US (MM/dd/yy) but that date didn't.

aware_support · Post by **aware_support** » Sun Aug 13, 2006 9:10 am

We tried the calculated field and it seems to work fine. Could you please provide specific steps required to reproduce the problem?

ab042 · Post by **ab042** » Tue Aug 15, 2006 4:20 am

Are you saying the Condition Evaluated to true in my previous message is NOT showing that way on your system?

-----------------
I'm having all kind of issues with the date in different search functions. You should note I'm using (MM/dd/yy) but also tryed (MM/dd/yyyy).

To better explain, please try the following:
#1) Go to the CRM sample app
#2) Menu STANDARD / SEARCH Object
#3) Search ALL Objects Appointments. Note one of the start time (I picked: 07/25/2006 11:00)
#4) Go Back and do a search using Search Objects Appointments using Main
#5) Key the START TIME as it appeared 07/25/2006 11:00 (Note nothing is returned)
#6) Now go Back to Search Objects Appointments using Main again. This time key the START TIME as 25/07/2006 11:00 Now it is displayed
(Note: I did find in the manual to do date search this way for what thats worth)

The problem is try a process or a query using a DATE that isn't keyed by the user, or one that is keyed in a normal date format like a user would be expected to use.
Better yet, try searching using a FIND statement with a calculated date (IE: CURRENT_DATE +5).

I am sure I'm missing something but I can't get this to work and I've reviewed and search the Doc's and tried different things for HOURS. Is there a change format on search command or something I'm missing.

If a date attribute has a format as (MM/dd/yy) logic would say that would also be the search format but it isn't. And it should be.

aware_support · Post by **aware_support** » Tue Aug 15, 2006 2:52 pm

We'll investigate and post a message to this forum when this is resolved.

aware_support · Post by **aware_support** » Wed Aug 16, 2006 8:27 am

This is now fixed starting from build 863.

There were a couple of problems:
1) dates were always interpreted in the dd/MM/yyyy format in form based search.
2) Wrong type of MySQL field was created for Date attribute (datetime instead of date). This affected correctness of queries involving dates. You will need to get your tables re-built using the new version to get this fixed. The easiest thing would be to re-create the business space and re-import the version.

ab042 · Post by **ab042** » Wed Aug 16, 2006 4:07 pm

Thank you. It appears it is NOT working. TWO other questions now come up if you could address them please.

#1) I created a new BO/imported my new exported one and created new test data. The Date Search as I explained below appears fine but if you try and use Date stuff in a rule/expression/process it doesn't appear to be working.

For EXAMPLE: I have a query that looks something like this.
BusinessOjbect.Ndate >= CURRENT_DATE (This works)
BusinessObject.Ndate < CURRENT_DATE (This works)
BusinessObject.Ndate = 08/15/06 (DOES NOT WORK)
BusinessObject.Ndate = 15/08/06 (WORKS)
BusinessObject.Ndate = CURRENT_DATE+5 (DOES NOT WORK)

Also PROCESSES that use CURRENT_DATE+5 (DOES NOT APPEAR TO WORK)
====================
#2) It would be VERY HELPFUL if we had QUERIES / PROCESSES and RESULTS of EXPRESSIONS in LOG VIEWER.
This would make all this hours of testing standout like a sore thumb. Process do show but the results of the expression don't. And it is very hard to debug.
For example, I have a process that has an express with CURRENT_DATE+5 in it. The log shows CURRENT_DATE+5 but not what its translating that to. And it shows Found 0 objects. Magic question is why? If I had the results of the express it would make debug very easy.
==================
#3) I understand the export/import into a new BS and have new tables in a new BS but I can't figure out how to get the existing data in them. If you will recall I attempted to address this simple backup restore issue in another thread awhile back, about exporting data in a CSV and importing it back in. But AwareIM doesn't give you the ability to export/import data in the same state into a NEW file.

Bottom line is how can we get the existing data in this new business space, that is required to fix this with our live data.

aware_support · Post by **aware_support** » Thu Aug 17, 2006 12:22 am

Sorry for all these troubles. There are still some limitations which you unfortunately hit - they do appear minor to us and there is a workaround:

BusinessObject.Ndate = 08/15/06 (DOES NOT WORK)
When you specify a date or time constant in rules it still has to be in dd/MM/yy or dd/MM/yyyy format. This does not seem to be a big issue since it only affects configurator, not end user.

BusinessObject.Ndate = CURRENT_DATE+5 (DOES NOT WORK)
Arithmetic with dates is not supported when used in queries. Please use DATE_ADD function instead:
BusinessObject.Ndate = DATE_ADD (CURRENT_DATE, 5)

Please also make sure that your database field is re-built and has "date" type, not "datetime" (see below).

#2) It would be VERY HELPFUL if we had QUERIES / PROCESSES and RESULTS of EXPRESSIONS in LOG VIEWER.
We understand this wish very well. Unfortunately, queries are directly translated into SQL and we do not perform intermediate calculations. It is not possible, though, to see all inner workings of SQL and understand why it doesn't find records. However, if you know SQL you can have a look at the SQL form of a particular query that gives its SQL translation.

#3) I understand the export/import into a new BS and have new tables in a new BS but I can't figure out how to get the existing data in them. If you will recall I attempted to address this simple backup restore issue in another thread awhile back, about exporting data in a CSV and importing it back in. But AwareIM doesn't give you the ability to export/import data in the same state into a NEW file.
There is actually much simpler way to change the type of the field. Go to mysql utility and type the following commands:
use BASDB (or use BASDBTEST if you are working in the testing mode)
ALTER TABLE BusinessSpaceName_BusinessObject CHANGE Ndate date
(if working in the testing mode BusinessSpaceName must start with bastestdomain, for example bastestdomainMyBusinessSpace_MyObject)

ab042 · Post by **ab042** » Thu Aug 17, 2006 1:40 am

No problem. Your input solved the issues I was having.

Thank you for your GREAT SUPPORT!!!!!!!!!!!!

ab042 · Post by **ab042** » Thu Aug 17, 2006 4:31 am

Now that I have the above working (Again THANKS) I'm having another issue with the 2nd part of my logic and after 5 hours I'm thinking it may be related.
Can you please tell me how to make an item appear on the existing Appointment calendar that is a DATE type field, not a timestamp type.

For example: I'm trying to set a StartTime attribute that is of a TimeStamp type with an attribute that is Date type.

Note: AttributeA = Type TIMESTAMP / AttributeB = Type DATE

RULE: AttributeA = AttributeB (I Can't seem to get this working)
For whats its worth the LogViewer shows: value was not changed because it was equal to the old value StartTime=null
(Since the logfile isn't showing results, how should I debug this? Other than to pull my hair out? I know AttributeB has a date I just keyed it.

What I really need to get done is to make the AttributeA have a time so it shows on the calendar. Something like AttributeA = AttributeB + '07:00am'

Please advise

aware_support · Post by **aware_support** » Thu Aug 17, 2006 5:43 am

It turns out that conversion from dates to timestamps and vice versa in assignment is not supported! And it should be. We will create a patch for this shortly and post a message to this thread.

aware_support · Post by **aware_support** » Thu Aug 17, 2006 11:47 am

This is now fixed starting from build 865

ab042 · Post by **ab042** » Thu Aug 17, 2006 4:43 pm

Ok, you fixed the 1st bug and then the next logic problem came into play and you fixed it.
And I should take the time to THANK YOU for your RESPONSIVENESS, it is truely awesome!

And now I'm doing what I believe is the final Query for this and run into another issue.

I created a query and set an attribute/expression to Ask at Run-Time. The Attribute is of DATE type and formatted as (MM/dd/yy) and when I select to run the query it ask for a Date as it should.

However, the FORMAT is not what the attribute is defined (MM/dd/yy). Its none US (dd/MM/yyyy), NOT SOMETHING THE USERS UNDERSTAND.

This causes MAJOR issues with users, because they don't use or want to use anything other than a format they use everyday. And I should note is the way they original keyed the date in the first place.

Please fix this one last thing and I promise and pray to leave you alone on this issue. Until the next one pops up

aware_support · Post by **aware_support** » Fri Aug 18, 2006 8:18 am

This is now fixed starting from build 866