Data Item - Date Range/Between Dates

[MJ]

Hi
First time posting but long time user.
I need some help....

I have a date field and what I would like to do is set up a data item to give a reply depending on the actual date.
eg.
case
when [Required By Date (Planned)] > 07/01/2017 and [Required By Date (Planned)] < 27/01/2017
then 'JAN'
when [Required By Date (Planned)] > 27/01/2017 and [Required By Date (Planned)] < 20/02/2017
then 'FEB'
end

when I first validate the report I get no issues. However when I add this to the report and run it I get errors. Then when I validate the query again errors come up.
So first things first, is the above example correct or is there a better way of setting this up.

Many thanks to any help received.

[Lynn]

Hi
First time posting but long time user.
I need some help....

I have a date field and what I would like to do is set up a data item to give a reply depending on the actual date.
eg.
case
when [Required By Date (Planned)] > 07/01/2017 and [Required By Date (Planned)] < 27/01/2017
then 'JAN'
when [Required By Date (Planned)] > 27/01/2017 and [Required By Date (Planned)] < 20/02/2017
then 'FEB'
end

when I first validate the report I get no issues. However when I add this to the report and run it I get errors. Then when I validate the query again errors come up.
So first things first, is the above example correct or is there a better way of setting this up.

Many thanks to any help received.

The error message you are getting might be helpful. Can you tell us what it is? What is the appropriate month when the date is 27/01/2017? It is neither JAN nor FEB based on your expression and there is no "else" clause to handle data outside the ranges you've defined.

[MJ]

Hi Lynne,
Thanks for the reply.
I've added the 'Else' part and attached an image of the error. Hope that gives you enough to go on.

Regards
Mike

[Lynn]

Hi Lynne,
Thanks for the reply.
I've added the 'Else' part and attached an image of the error. Hope that gives you enough to go on.

Regards
Mike

Well the error message is very clear about what the problem is. Read the bit that says "inconsistent data types: expected DATE got NUMBER"

This tells you that you are mixing data types in your comparison. Either your Required by Date field is number rather than date, or else you need to cast your 07/01/2017 to a date datatype.