Skip to Content
author's profile photo Former Member
0
Former Member

Convert String into Date

Posted on Nov 21, 2011 at 10:55 AM 797 Views

Hello everbody,

first of all I have to admit that i'm not a specialist in SQL and SAP BO. Perhaps my question isn't that difficult as i think.

In my report I have a fix date as a string, e.g. "October 2012". I have the problem that I have to compare that date with another one to get the months between those dates.

I tried to tranform the string into a date format but I wasn't successful. So I started to get the month with the substr command in the formula editor:

Measure Name: String_Month

  =Substr([Replacement Date];1;Pos([Replacement Date];" ")-1)

So the report gives me "October" back. In the next step I tried the month() function to convert the month string (October) into the month number. But that doesn't work.I get an error message that the data type is wrong, although I have a string.

Measure Name: Number_Month

 =Month([String_Month])

So my question is, how can I convert the "October 2012" into a date?

Thanks for any suggestions.

regards Kai

Edited by: Kaitras on Nov 21, 2011 11:57 AM

Edited by: Kaitras on Nov 21, 2011 11:58 AM

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2011 at 11:39 AM
    0

    Hello,

    What ever you specified is correct.When you pass string(October) the system will write Only 10.As you need to find teh date of the Month then you need to pass the string like 12Oct2012.then the system will write 12.10.2012.

    Hope this may help..!!

    Regards

    SreeKumar

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2011 at 11:46 AM
    0

    Hi

    How can you convert string into date type , if it is calendar day the data type is date ,for month it is character type . we can change date type to string but conversely it is not true .

    i have two alternatives

    1)you change the target date datatype to string and then compare it

    or

    if u want only date date data type then

    2) Universe level you can change the data type as date do according to your requirement . then you can compare with other dates .

    hope this helps u

    sunil

    Edited by: K.sunil on Nov 21, 2011 1:00 PM

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Nov 22, 2011 at 10:43 AM

      Hi,

      My first reaction to this is:

      Month(date) returns a string name, never a number and for that you have to give a date as a parameter.

      So: Month("October") doesn't give you anything

      Month(12/12/2011) returns "December"

      Regarding your issue:

      First we need to convert string to date

      Unfortunally there is no formula for converting a month name into number so you have to use IF

      after using IF lets say

      m=if(x="January then "01" elseif x="February" then "02 ....

      ToDate(date_string;format) will solve this so please try this:

      a=Concatenation("1/";m;"/2012")

      Then ToDate(a;"dd/mm/yyyy")

      Finally use MonthsBetween and you will have your solution

    Comment on This Answer
  • author's profile photo Former Member
    Former Member
    Posted on Nov 24, 2011 at 05:20 AM
    0

    Hi Kaitras,

    You want to convert a month/year to date, for eg: the one you have given is October 2012. It is possible.

    You need to do is to write some functions as follows:

    1. First of all extract October from the October 2012 string, for that you need to write function Left("October 2012";Pos("October 2012";" ")) and save it in a variable "ABC"--- this will give you October.

    2. Write an If condition like this If(ABC = "October";ToDate("10/1/"+Right("October 2012";2);"M/d/yy");.This will give you first date of October(10/1/12 - mm/dd/yy format)

    3. Then you can use LastDayOfMonth() to find last day of October, RelativeDate() to find any day in October.

    4. Similarly you can do the same for other months.

    Regards,

    ArunKG

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.