Date check

[Ronal_bal]

Can somenone explain what is wrong with below formula? 

when i input 20230312 is return null

IF REGEX_Match([date], "^(19|20)\\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\\d|3[01])$")
THEN DateTimeFormat(DateTimeParse([date], "%Y%m%d"), "%Y-%m-%d")
ELSE NULL()
ENDIF

[JosephSerpis]

Hi @Ronal_bal you have some extra slashes in your regex if your remove them your formula should work.

IF REGEX_Match([date], "^(19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])$")
THEN DateTimeFormat(DateTimeParse([date], "%Y%m%d"), "%Y-%m-%d")
ELSE NULL()
ENDIF

[DataNath]

Hey @Ronal_bal, you're using \\d in 2 spots. Using a second backslash acts as an escape character to allow the RegEx pattern to look for '\' rather than the '\' of '\d' representing the fact that you're looking for a number. Therefore, if you just remove the additional backslash in both of these occasions your formula expression ought to work - I've just tried it and it parses the date as expected:

IF REGEX_Match([date], "^(19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])$")
THEN DateTimeFormat(DateTimeParse([date], "%Y%m%d"), "%Y-%m-%d")
ELSE NULL()
ENDIF

[Ronal_bal]

Can i know why we use \\  in the regex? @DataNath @JosephSerpis 

[DataNath]

Hey @Ronal_bal, you can use \ to escape a character that would otherwise have a set purpose. For example, if you wanted a RegEx statement to look for a full stop, you'd need to use '\.' in order to escape the dot, as '.' is a special character used to represent any character. Likewise for '*' - this is used to represent zero or more of the character it comes after, so if you wanted to look for an asterisk as its own character, you'd need to use '\*'. In your example, \d represents any digit 0-9 and by using '\\d' you had escaped the '\' of that and were therefore telling your RegEx statement to look for a backslash followed by 'd'.