Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Raj_007
8 - Asteroid

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

3 REPLIES 3
IraWatt
17 - Castor
17 - Castor

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

Raj_007
8 - Asteroid

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

IraWatt
17 - Castor
17 - Castor

Awesome ! no worries

Labels