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

Date conversion

Hi All,

Sub: I wanted to convert a tring field to date format.

Details: I have a SAP table S084 and it got a field SPMON this SPMON is string(6), when I directley display this field in Crystal its displaying 200309, that is year 2003 and month SEP that is 09.

I wanted to convert this YYYYMM string to Date format like

if it is 200001 i.e 01/01/2000, and 200209 i.e 09/01/2002 like this.

Any help is greatly appreciated!

Thank you,

Ashok

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 18, 2008 at 03:14 PM

    This may not be the best way, but here is an easy fix that I would do.. having limited knowledge of CR

    for date 200309... you could use the "Left" string formula to pick out the first 4 numbers... then use the "Right" to pick up the last 2 numbers.

    From here you could Concatenate the fields together..... You could even add another formula to add the 01 for the day of the month (formula being "01") and "/" to add the slash between month/day/year.

    for 1st 4 numbers........ Left (,4 ).... for year for last 2 numbers......Right (,2 ).... for month

    for day of month......... "01" (formula named 1 for this example)

    for slash...................."/" (formula named slash for this example)

    to Concatenate together it would look something like this:

    {@right} + {@slash} + {@1} + {@slash} + {@left}

    result 09 / 01 / 2003

    This would all result in the field being a string field... if you are looking for a date field I doubt this would work.

    Or the experts could chime in with an easier fix for you!

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 18, 2008 at 03:29 PM

    Hi,

    Travis's solution will work fine, in addition, if you want the output to be in date format, you can use the DateValue() function as follows:

    datevalue(tonumber(left({@dateField},4)),tonumber(right({@dateField},2)),01)

    Cheers,

    Fritz

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 18, 2008 at 05:15 PM

    It worked well as expected.

    Thank you so much!

    ---Ashok

    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.