Format Date

system · September 15, 2009, 6:26am

Hi Friends,

I am working on the Date formats but I am little bit confusing for changing the date formats.

Below is the task.

I have to change the date format to dd/MMM/yy for one object.

Actually I have one object called Daydate. I have set up a prompt for Daydate object in webintelligence query panel as Daydate between “start date” and “End Date”. up to now everything is fine . I have ran the report and I have retrieved the start date and end date value in the header of the report. for this retreving I have created a object called Var_start date object and placed this formuale

=Replace(UserResponse([MainQuery]; “Enter Start Date:”);" 12:00:00 AM"; “”).

After creating this object I have placed in the report header the date is displaying the format of 5/1/2009 But I want to display the date in the format date of dd/MMM/YY. for this I have apllied FormatDate function for the Var_start date. but I am getting the error like " the expression and sub expression has invalid data type.

Could you please suggest me reagrding this.

Regards,
Eswar

eswar519 (BOB member since 2009-05-26)

system · September 15, 2009, 6:34am

Please post the FormatDate() code.

Jansi (BOB member since 2008-05-12)

system · September 15, 2009, 6:43am

Hi,

FormatDate( [var_Start_Date];“dd/MMM/yy”);

Regards,
Eswar

eswar519 (BOB member since 2009-05-26)

system · September 15, 2009, 6:48am

UserResponse and Replace will result in a string. So use ToDate() to convert it to Date type and then use FormatDate() to achieve the desired format.

Jansi (BOB member since 2008-05-12)

system · September 15, 2009, 7:10am

=Left(UserResponse([Main Query;"Enter Start Date:");10)

Or

=FormatDate(ToDate(UserResponse([Main Query];"Enter Start Date:");"dd/MM/yyyy h:ss:mm a");"dd/MMM/yy")

aniketp (BOB member since 2007-10-05)

system · September 15, 2009, 8:00am

I have applied the Todate function it is becoming Date data type up to now everything is fine and next I have applied Formatdate function to the Start date but It is automatically changing to String and I am getting the output #Error. Could you please suggest me why this error is getting.

Regards,
Eswar

eswar519 (BOB member since 2009-05-26)

system · September 15, 2009, 8:36am

Please post the exact code that you use.

Jansi (BOB member since 2008-05-12)

system · September 15, 2009, 8:41am

Eswar, did you try this formula -

=FormatDate(ToDate(UserResponse([Main Query];"Enter Start Date:");"dd/MM/yyyy h:ss:mm a");"dd/MMM/yy")

Even I was facing #Error, but after applying FormatDate it was solved.

aniketp (BOB member since 2007-10-05)

system · September 15, 2009, 8:57am

I am getting the same error below is the formulae I have used in the report

=FormatDate(ToDate(UserResponse([MainQuery];“EnterBegining Date:”);“dd/MM/yyyy h:ss:mm a”);“dd/MMM/yy”)

==FormatDate(ToDate(UserResponse([MainQuery];“Enter Ending Date:”);“dd/MM/yyyy h:ss:mm a”);“dd/MMM/yy”)

Regards,
Eswar

eswar519 (BOB member since 2009-05-26)

system · September 15, 2009, 8:59am

Could you please let us know what is the exact format of the date that you enter/select?

Jansi (BOB member since 2008-05-12)

system · September 15, 2009, 9:52am

I am selecting M/d/yyyy

Regards,
Eswar

eswar519 (BOB member since 2009-05-26)

system · September 15, 2009, 9:56am

If you are selecting/entering the date in M/d/yyyy format, you’ve to use M/d/yyyy as the second parameter of ToDate() function.

Jansi (BOB member since 2008-05-12)

system · September 15, 2009, 9:56am

Use

=FormatDate(ToDate(Substr(UserResponse([MainQuery]; "Enter Start Date:");1;9);"MM/dd/yyyy");"dd/MMM/yy")

Cheers

Prashu (BOB member since 2005-08-17)

system · September 15, 2009, 10:06am

I am getting the same error. Below is the code I have used

=FormatDate(ToDate(UserResponse([MainQuery];“Enter Begining Date:”);"M/d/yyyy h:ss:mm ");“dd/MMM/yy”)

Regards,
Eswar

eswar519 (BOB member since 2009-05-26)

system · September 15, 2009, 10:09am

Try this.

=FormatDate(ToDate(UserResponse([MainQuery];"Enter Begining Date:");"M/d/yyyy");"dd/MMM/yy")

Jansi (BOB member since 2008-05-12)

system · September 15, 2009, 10:20am

Hi

Atlast I got the solution I have used this code

Code:
=FormatDate(ToDate(Substr(UserResponse([MainQuery]; “Enter Start Date:”);1;9);“MM/dd/yyyy”);“dd/MMM/yy”) .

Thanks for all to get these solution.

Regards,
Eswar

eswar519 (BOB member since 2009-05-26)

system · September 15, 2009, 10:45am

I tried the same code, it was not working for me, then I used the below code and it worked(that’s why I gave this as a solution ).

=FormatDate(ToDate(UserResponse([MainQuery];"Enter Begining Date:");"M/d/yyyy h:ss:mm a");"dd/MMM/yy")

I guess in the above code you missed “a”, so it was not giving right results.

aniketp (BOB member since 2007-10-05)