converting date field which is in string format into Date format and Add 1 year...error

Raj_007

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

IraWatt

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