Saturday, January 16, 2010

Difference between Today() and TimeStamp() while used in Calculated field !!

Here is something interesting I found today while working on a requirement in which we need to capture the timestamp of the record when a field get updated.

So the exact requirement was :

Capture the timestamp of the record when the Status of the Service Request changes to Approved. The field used for this purpose named as "SR Approved On".
Now this requirement seems very simple and the very first idea come into any Developer's mind is that use the BusComp user property for this i.e. "On Field Update Set" and set the value for "SR Approved On" using Today(). I did the same thing and here is what I tried:

1. Create a BC User Property :
a) Name : On Field Update Set 1
b) Value : "Status", "SR Approved On", "IIF ([Status] = 'Approved', Today(), "")"

Everything looks okayy, but the only thing I wonder is that "SR Approved On" stores the correct date but not the time. "Time" it stores as "12:00:00" for each record.

Hmmmm.... what could be reason?? If you are thinking that underlying column for "SR Approved On" field is only date field, then this is not the case here. I verified the below things:

a) Underlying column for "SR Approved On" is of type "UTC Date Time".
b) Type of "SR Approved On" at the BusComp level is "DTYPE_UTCDATETIME".
So, this fact is totally ruled out that "SR Approved On" can only store Date but not time. That means problem is somewhere else or may be "Today()" is returning only Date not time. But still I didn't convince with this because I am in the impression like we use sysdate in Oracle SQLs it should work that way.

To verify this, I created a Calc Field on Service Request BC:
a) Field Name : Current Time
b) Calculated : True
c) Calculated Value : Today()
Exposed the field on Service Request List Applet and to my surprised I found that it displays the current date but time as "12:00:00 AM" for all the records.

Well, that means we cannot get current timestamp out of "Today()". I need to used something else and let me tell you this was my totally wild guess of using "TimeStamp()" function, which I never used before and it worked pretty well when I changed the Calculated value to "TimeStamp()".

Finally, I got the issue resolved, just changed the User property value to :

a) Name : On Field Update Set 1
b) Value : "Status'', "SR Approved On", "IIF ([Status] = 'Approved', TimeStamp(), "")"
Might be helpful for you, if not used it before !!

.

6 comments:

  1. Thanks for the Info..It was really helpful.

    ReplyDelete
  2. Gaurav,

    If I have to write a calculated field where I want the age of a Lead. Basically which means Created Date Time -Time today. I was thinking to use. ([CREATED] - TimeStamp() )
    I am just unsure if {Created} will have the timestamp. because I am looking for exact date and time.
    Thanks in advance..
    Poonam

    ReplyDelete
  3. [Created] would definitely give you the time as well, but to find out the difference between the two dates, you have to use Julian functions (JulianYear/Month/Day) and find out the days difference somethng like:
    JulainDay([TimeStamp]) - JulianDay([Created])

    if you are looking for date time difference as well in seconds, eScripting is the only solution using getHours(), getMinutes, getSeconds() functions.

    ReplyDelete
  4. Hi Gaurav,

    How can we update field as read only for newly created records only not for old records..is timstamp() function will work here to make field read only for newly created records?

    Thanks
    Abhas

    ReplyDelete
  5. hello,
    looking for help calculating the difference in days between the date that the Opportunity was created and the closed date. How does one do this in Analytics/Answers.

    ReplyDelete
  6. try to test both timestamp() and today()...whereby for Today() it will add-on with GMT time zone...

    Let's say Im on Singapore,Kuala Lumpur time zone (GMT +8 hours), Today() function will only populate the (actualy hours - 8 hrs)...You might need additional calculation to get the exact date-time.

    ReplyDelete