TextToDate() woes

TextToDate() woes

  
Hellow everyone,
In a filter building expression, I want to allow the user to enter a date to search by so I am using the followin expression
TextToDate(SearchKeyword)

My problem is at run-time. When the user enters a date "2012-12-30" it runs fine but when the user enters "30-12-2012" I get the following run-time error message:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value
A couple of questions here:
1- Is there a built in function like "IsDate()" to inspect a text input and retrun True/False if the input text can be converted to a date value

2- Why is this failing depending on the format of the date entry and how to overcome it?
Hi Shraim,

Based on your post you have only one text field to allow the user to type anything, if that's the case you must use the convertion from text to date. 

Answers to yours question.

1- You can use the built-in function TextToDateValidate to make sure that you can convert into date.
http://www.outsystems.com/help/servicestudio/7.0/Language_Reference/Built_in_Functions/Data_type_conversion_functions/TextToDateValidate_Function.htm

2- Here you have two options:
  • Create a second field only for fitler based on dates;
  • Create some logic (perhaps using a custom function) to transform your string into a readable date (eg. searching for date separators and moving the year from the end into the begining);
Regards,
Paulo Garrudo
Paulo Garrudo wrote:
Hi Shraim,

Based on your post you have only one text field to allow the user to type anything, if that's the case you must use the convertion from text to date. 

Answers to yours question.

1- You can use the built-in function TextToDateValidate to make sure that you can convert into date.
http://www.outsystems.com/help/servicestudio/7.0/Language_Reference/Built_in_Functions/Data_type_conversion_functions/TextToDateValidate_Function.htm

2- Here you have two options:
  • Create a second field only for fitler based on dates;
  • Create some logic (perhaps using a custom function) to transform your string into a readable date (eg. searching for date separators and moving the year from the end into the begining);
Regards,
Paulo Garrudo
 
Thank you Paulo for help. While I did find the TextToDateValidate() in the help, the expression builder dialog does not list this function along with the other Data Conversion functions. Also when I typed the function manually the expression builder just objected that this function is unkown (image shown).


 
You are not able to use it inside the query, you must do it outside (check the image below).

Just some remarks to my code:
  • My query parameter is a "search parameter" it will allow to skip any condition are is used if the value is null;
  • It was necessary to convert the User.CreationDate from Datetime to Date, but you can build it based on your logic;
  • All the logic for check the filter format is outside the query: If(TextToDateValidate(Filter),TextToDate(Filter),NullDate())



Regards,
Paulo Garrudo
Thank you Paulo. I actually wanted to keep a single filter field for the user to enter the searchKeyword including that for Date. What I am thinking to do now is to build a user function that takes a Text value and converts it to a date value as long as the text value is a valid date in any format not just the yyyy-mm-dd otherwise it would return a NullDate.
I would appreciate your personal opnion on this workaround.
That was one of the options that I suggested on my original reply.
You can have some problems when trying to check if a certain number is a month or a day (01 to 12), besides this it should be just "playing" with strings.

Paulo
Hi,

I have a similar problem.
In my query I have an Input Parameter DateIn with type Date and in the query condition I have:
DateTimeToDate(TableName.DateTimeField) = DateIn and get the same error stated above...
In my case DateIn is a valid Date value (ex: 2014-04-04).

Any thoughts?

Thanks in advance.

Cheers,
JA