B2C Servicepublic

Reporting and Analytics for Service

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Mohana Gopal Selvam
    Convert plain text field to date time format
    Topic posted June 18, 2015 by Mohana Gopal SelvamSilver Crown: 22,500+ Points 
    634 Views, 14 Comments
    Title:
    Convert plain text field to date time format
    Content:

    Hi Gurus,

    We have received data like 'dd-MM-yyyy HH:MM' (04-06-2015 01:29). We have loaded this data in our custom object in string field. We are trying to show this field in a report as a date time format. If it is string we can not sort this field (ascending or descending). Is there anyway to convert it?

    Thanks,

    Mohan

    Comment

     

    • Sebastiaan Draaisma

      Hi Mohan.

      Did you try to_date(str, format)

    • Andrew Wauchope

      Hi Mohan

      You'll need to create an expression and use the function below to convert the string to a date format.

      to_date (str, format) -- Converts the value entered in str that is in the format specified in format to a date or date/time value. For example, the expression to_date('20090215','YYYYMMDD') returns a value of 02/15/2009. 

      Before doing this though, you may need to convert the string to a number so it strips out all the - and : you currently have in the string.

      to_number(str) -- Converts the character string to a numeric value. If the string is not numeric, this function will return zero. If the string is a combination of numbers and other characters and begins with a number, this function will return only the initial numeric portion. For example, to_number(123ABC) will return 123. 

      The end result is going to look something like:

      to_date(to_number(field.name), DDMMYYYY HH12:MI PM)

      You may have to play with this a bit to get it exactly how you want, although I do think this is what you are after.Hopefully this helped.

      Andy

    • Mohana Gopal Selvam

      @Uniscan,

      Thanks. We have to show the date and time in the report. We could not use to_date function since it is return only date.

      @Andrew,

      Thanks for your response. We are expecting something like to_date(field.Name,'DDMMYYYY HH12:MI PM'). But its throw an error.

      Thanks,

      Mohan

    • Sebastiaan Draaisma

      In that case I would use the to_number(str) in a column that you make invisible (just for sorting) and another column for more readibility with for example 'Year ' || substr('20150618', 1, 4) || ' Month ' || substr('20150618', 5, 2) || ' Day ' || substr('20150618', 7, 2)

    • Sebastiaan Draaisma

      And to include the time stamp:

      'Year ' || substr('201506180915am', 1, 4) || ' Month ' || substr('201506180915am', 5, 2) || ' Day ' || substr('201506180915am', 7, 2) || ' Time ' || substr('201506180915am', 9, 2) || ':' || substr('201506180915am', 11, 2) || if(substr('201506180915am', 13, 1)='a', ' AM', ' PM')

      Of course this is just an exemple on how you can format your string.

    • Mohana Gopal Selvam

      Thanks for your detailed answer Uniscan.

      Sort is not displayed correctly, when we click the date field (String type) in the report since it is the string. That's why we are expecting how to convert the string field to date time.

      Thanks,

      Mohan

    • Sebastiaan Draaisma

      Could you give me a sample of your data in your custom field?

    • Mohana Gopal Selvam

      Please find the screenshot. I sorted in ascending. But it displayed in wrong manner.

      Thanks,

      Mohan

      48900Capture.JPG (33KB)
    • Sebastiaan Draaisma

      Use the following data in a column to sort on :-)

      substr('29-05-2015 06:03', 7, 4) || substr('29-05-2015 06:03', 4, 2) || substr('29-05-2015 06:03', 1, 2) || substr('29-05-2015 06:03', 12, 2) || substr('29-05-2015 06:03', 15, 2)

    • Sebastiaan Draaisma

      It will then be sorted on yyyymmddhhmm

    • Mohana Gopal Selvam

      Hi Uniscan,

      If I use substr as you suggested, we may get the output like yyyymmddhhmm. But the client expectation is DD-MM-YYYY HH:MM format. Last option, we have to use datetime field instead of string.

      Is there any other option?

      Thanks,

      Mohan

    • Sebastiaan Draaisma

      I would just make the column with the substr code invisible in the report (you can still sort on the invisible column) and have the visible column showing DD-MM-YYYY HH:MM

      Everybody happy :-)

    • Mohana Gopal Selvam
      Thanks Uniscan. The invisible column will use it first time report liad. But when the agent clicks on created date on the report, it will show the wrong order only. 

Thanks,
Mohan
    • Sebastiaan Draaisma
      That is correct. You might be able to set the other columns to no sorting but I am unable to confirm if this will give the desired effect as I have no access to RN from my Android.

     

    community forum