Skip to Content
avatar image
Former Member

Date parameter conversion issue

Hi,

I have to convert a user input date DDMMYY (Number) to YYYYMMDD(DECIMAL or NUMBER).

The user enters the date in the input screen in ddmmyy and it is compared in selection criterion with a database field

which is yyyymmdd format.

Both are of type number. Please let me know how to achieve this

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 20, 2008 at 04:13 PM

    Hi Shalu,

    Use the Following Steps:

    1.Firstly,Create a parameter field with a type,Number.

    2.To remove all space's and thousands delimiter In the parameter field ,write a formula

    **FRLDATE*

    Replace(totext{?parameter name},"##"),",","").

    3.Create another formula ,

    stringVar a;//year

    stringVar b;//month

    stringvar c;//day

    a:=totext(right({@frlDate},2));//year

    b:=left(mid({@frlDate},3),2);//month

    c:=Left({@frlDate},2);//day

    "19" & (a) & "/" & (b) & "/" & (c);

    Hope,this helps you .

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 20, 2008 at 12:46 PM

    have you looked at using the Isdate() and DateValue() functions?

    regards,

    dom

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 20, 2008 at 02:02 PM

    Hi Shalu,

    Can you try the following in selection expert :

    numbervar y:= tonumber("20" & Mid (totext({?parameter}),5,2));

    numbervar m:= tonumber(Mid (totext({?parameter}),3,2));

    numbervar dd:= tonumber(Mid (totext({?parameter}),1,2));

    DatabaseField = cdate( y, m, dd);

    Thanks,

    Sastry

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 20, 2008 at 02:29 PM

    Hi Shalu,

    When converting from a 2 digit yeat to a 4 digit year, you have to make sure the century is correct. Try this formula I have. Not the year portion checks to see if the year belongs in 1900 or 2000.

    I took the parameter field and converted it to a text variable. From there I extracted the year, month and day; converted them back to numbers and simply added them together.

    StringVar MyDate; 
    NumberVar MyYear;
    NumberVar MyMonth;
    NumberVar MyDay;
    
    MyDate := ToText ({?X}, 0, "", "");
    
    If MyDate [5 to 6] > (Year (Today) - 2000) Then 
    	MyYear := 1900 + ToNumber (MyDate (MyDate [5 to 6]
    Else MyYear := 2000 + ToNumber (MyDate (MyDate [5 to 6];
    
    MyMonth := ToNumber (MyDate [3 to 4]);
    
    MyDay := ToNumber (MyDate ([1 to 2];
    
    (MyYear * 10000) + (MyMonth * 100) + MyDay;

    Good luck,

    Brian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 20, 2008 at 05:28 PM

    Hi,

    thanks everyone for their responses.

    I have resolved this by using

    stringvar yyear;

    stringvar mmonth;

    stringvar dday;

    yyear:= {?S1DATE}[5 to 6];

    mmonth := {?S1DATE}[3 to 4];

    dday := {?S1DATE}[1 to 2];

    if yyear < "50" then

    date(tonumber(yyear)+2000,tonumber(mmonth),tonumber(dday))

    else

    date(tonumber(yyear)+1900,tonumber(mmonth),tonumber(dday))

    The issue I was facing was because in the selection criterion the datatype of the database field was number whereas the conversion formula was returning number. I converted both database and parameter value to dates to evaluate the conditon:

    table.column >= {>S1date}

    Its working after converting both the above to dates independently and then evaluating.

    Thanks,

    Shalu

    Add comment
    10|10000 characters needed characters exceeded