topic Convert text to date in University Forum

Convert text to date

SamPark — Mon, 22 Jun 2020 00:43:00 GMT

Appreciate your guidance on conversion as I am getting an error when attempting to convert date in text value to date.

I use a following expression in calculation stage to convert text value and store it in new data stage with date data type:

FormatDate(ToDate ([Departing date]), "dd/MM/yyyy")

20/07/2020 text value is stored in data stage named "Departing date".

Can't seem to understand why I am getting an error message of "Unable to convert supplied value to a date: Text value "20/07/2020" cannot be interpreted as a date.

Expression appears valid.

------------------------------
Sam
------------------------------

RE: Convert text to date

PatrickAucoin — Wed, 24 Jun 2020 14:40:00 GMT

Hi Sam,

I think I was able to reproduce the error as you have described it.

When you create the text data item, don't use punctuation/quotation marks when entering the value. Removing the punctuation should resolve the error.

------------------------------
Patrick Aucoin
Senior Product Consultant
Blue Prism
------------------------------

RE: Convert text to date

Dexter_Whelan_SAS — Thu, 25 Jun 2020 09:07:00 GMT

Hey Sam,

check your system date. Likely your non-UK based date format is causing the issue. The issue being a month with that date doesn't exist. Try going into either your system settings and changing how your dates are interpreted or going into your quotes file and changing how the dates are generated to match your date formats. If you switch the date around im sure you'll notice it suddenly works i.e. from 20/07/2020 to 07/20/2020.

------------------------------
Dexter Whelan
Senior Developer/Mentor
SAS
Europe/LondonDexter Whelan
Senior Developer/Mentor
SAS
Europe/London
------------------------------