Converting a date but calendar still shows it is as numeric value

[#1]

I am passing a feld from my DB in as a string by converting it on script load like so

CONVERT(varchar, my.db.field, 103) AS 'UpdatedDate'

Now this shows fine in my chart as in the string format i was expecting.

I have set up 2 variables(StartDate/EndDate) that change each time a user selects from a calendar, but it is only bringing me back a numeric value, which is then not filtering my data

I have the Min and Max values of the calendar properties set to:

=Min([UpdatedDate])

=Max([UpdatedDate])

Any ideas why this is not working?

I have it working perfectly fine on another report, but it will not work for some reason on this one.

Regards

Max

[settu_periasamy]

Hi,

may be try to change your format in the Number Tab for Calendar Object.

Calendar Properties -> Number Tab-> Override Document Settings -> Date

Or

=Date(Min([UpdatedDate]))

=Date(Max([UpdatedDate]))

[#3]

That does change the format so it looks like a date, but does not filter my results.

I dont understand how the datepicker is changing it into an int as i'm specifically converting it as a string in the script load

[Gabriel]

Hi,

You probably need to wrap your variable in Date format function.

LET vMin =  Date(Min([UpdatedDate]), 'DD-MMM-YYYY');

LET vMax = Date(Max([UpdatedDate]), 'DD-MMM-YYYY');

This should resolve the problem

[PradeepReddy]

try some thing like this...

Let vFromDate=Floor(Date#(Min([UpdatedDate]), 'YYYY-MM-DD'));

Let vToDate=Floor(Date#(Max([UpdatedDate]), 'YYYY-MM-DD'));

In Properties,

Calendar Properties -> Number Tab-> Override Document Settings -> Date format

[#6]

thanks for all the replies but none of them actually worked.

i ended up getting round this by creating a button with an action on it that has an expression on it like so:

='>=' & Date(StartDate, 'DD/MM/YYYY') & '<=' & Date(EndDate, 'DD/MM/YYYY')

That then filters out my results as required