Convert String into Date

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

Thx for the fast answers, but in this case i need the time between two dates like 01.10.2011 and 31.10.2012.

The major problem is that in the Database the finish date is a string an misses the actual day. So I only have e.g. "October 2012".

I put manully an acutal day so I get 31.October 2012 but also here the ToDate funtion doesn't work.

I know that the syntax of the date string is wrong because it isn't 10/31/2012.

 =ToDate([Replacement Date]; "MM/dd/yyyy") 

How can I format the string 31.October 2012 to a date 31.10.2012 ? Anybody an idea? I would be very grateful.

regards Kai

Edited by: Kaitras on Nov 21, 2011 2:29 PM

[Former Member]

Hi

First of all how u would like to show data ? i didnt understand ur requirement ,i think u want to show the 1-31 dates in report r8?

Edited by: K.sunil on Nov 21, 2011 2:43 PM

[Former Member]

I need these to dates for calculation, e.g.:

Cost in year 2011: 10,000 $

Fix Date: 01.10.2011

Replace Date: 31.July 2012

so i got 10 month between these two dates (including October).

So I need the months between these two dates to calculate the cost for 2012. The Problem is the Replace Date comes from a database where the data type is a string and I only have the month and the year (Replace Date: July 2012). So I can't use the monthBetween function to calculate.

I hope that makes it a lot clearer.

[Former Member]

Hi

why wont u use calendar day in query filter by using calanderday between from and to dates so that u will get required data , i am sure u will get it ,

Hope this helps

[Former Member]

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

[Former Member]

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