Cast string to date with specified pattern

[ANDREY]

Hello, I have a string representation of the date - "01.02.2016", and I need to cast it to datetime with specific pattern "dd.MM.yyyy" to prevent wrong casting in another culture settings (e.g. en-us which will interpret it by pattern "MM.dd.yyyy").

How can I do this?

Thanks a lot!

[Ivan Hristov]

Hello Andrey,

There is no a built-in way of providing the format when parsing a date. The recommended solution would be to create a User Function that accepts the string as argument and returns a DateTime instance according to the specified pattern. Another option is to convert the string to Date using the Date(year, month, day) function. The arguments can be provided using the Substr() function and each substring must be converted to an integer with CInt() to satisfy the Date() function requirements. In this case the expression would look like this:

=Date(CInt(Substr(Fields.DateStr, 6, 4)), CInt(Substr(Fields.DateStr, 3, 2)), CInt(Substr(Fields.DateStr, 0, 2)))

Hope this helps.

Regards,
Ivan Hristov
Progress Telerik

[Vishal]

Hi Ivan,

What if Fields.DateStr is null. It gives input string is not in correct format. Can we use default value?

[Todor]

Hi Vishal,

You may use the Null-coalescing Logical operator (??) or IsNull(value, defaultValue) Conditional Function.

[ANDREY]

Thanks!