Hi All, Thanks for your time
I have a string field in SQL server which includes the date values in mm/dd/yyyy format for example ( 04/22/2022) this field also includes some text values like "Good", N/A" -
I need to add 1 year to the date values only excluding the "Good","N/A - so i tried the following but iam getting the "month number out of range error"
I cannot use datetime tool because i have to handle string values...so what i have is
1).First convert into date format in the formula tool
2).Then add 1 year to that date value
3).again convert into mm/dd/yyyy format
Error: ConvError: Formula (633): DATETIMEPARSE: Cannot convert "02/23/2022" to a date/time with format "%Y-%m-%d": Month number is out of range 1..12: '23/2022'
Formula tool: Data type of this field is string
IF ISNULL([DR - Plan Signoff Date]) OR [DR - Plan Signoff Date] IN ('Gap','N/A') THEN [DR - Plan Signoff Date]
ELSEIF [Category] IN ('CDOB','Non-CDOB') AND ( !IsNull([DR - Plan Signoff Date]) OR [DR - Plan Signoff Date] NOT IN ('Gap','N/A') )
THEN DateTimeFormat(DateTimeAdd(DateTimeParse([DR - Plan Signoff Date], "%Y-%m-%d" ) ,1,"year"),"%m/%d/%Y")
ELSE [DR - Plan Signoff Date]
ENDIF
any guidance please
Solved! Go to Solution.
Hey @Raj_007,
Looking at that error it looks as though its trying to parse "02/23/2022" with a format "%Y-%m-%d". What it should be doing is parsing it with "%d/&m/%Y". Perhaps add an exstra step such as: AND Contains([DR - Plan Signoff Date] , "-") eg.
IF ISNULL([DR - Plan Signoff Date]) OR [DR - Plan Signoff Date] IN ('Gap','N/A') THEN [DR - Plan Signoff Date]
ELSEIF [Category] IN ('CDOB','Non-CDOB') AND ( !IsNull([DR - Plan Signoff Date]) OR [DR - Plan Signoff Date] NOT IN ('Gap','N/A') ) AND Contains([DR - Plan Signoff Date] , "-")
THEN DateTimeFormat(DateTimeAdd(DateTimeParse([DR - Plan Signoff Date], "%Y-%m-%d" ) ,1,"year"),"%m/%d/%Y")
ELSEIF Contains([DR - Plan Signoff Date] , "/") THEN DateTimeFormat(DateTimeAdd(DateTimeParse([DR - Plan Signoff Date], "%d/&m/%Y" ) ,1,"year"),"%m/%d/%Y")
ELSE [DR - Plan Signoff Date]
ENDIF
Thank you IraWatt - got it, i need to pass what format i have in my incoming field. I only have the this format in my incoming field mm/dd/yyyy- so i used
DateTimeFormat(DateTimeAdd(DateTimeParse([DR - Plan Signoff Date],'%m/%d/%Y'),1,"year"),'%m/%d/%Y')
Thanks again
Awesome ! no worries