Date Conversion or Date Parsing

[YiC]

Hi,

I have a column in Excel file that indicates Year and Month as “yyyy0MM”  (for example 2020007 for July 2020). I need to convert it to a  “yyyyMM” format (202007). I am working in-database so I need a SQL expression to convert this column to the YearMonth format and then I need to use this YearMonth field to set parameters on what accounts to pull based the month they are originated. The original format of this column is V-string. Can someone help me with this SQL expression?

[echuong1 · Accepted solution]

Are you working on this in SQL, or Alteryx?

In Alteryx, you can import the data within a Input Data tool. After, you can use left and right functions to grab the month and year values. You won't need the tostring() if your values are already vstring.

left(tostring([Field1]),4)+right(tostring([Field1]),2)

[YiC · Accepted solution]

I am working in Alteryx in-database.

I tried your formula left ([Field1],4)+right([Field2],2) it worked. Thanks!

[YiC]

I have a subsequent question when I use this field in combination with an "origination_date" field to define new account based on the month the account is originated. I created a new field "New_Indicator" and it is defined by SQL:

CASE WHEN (YEAR('Origination_Date')*100)+(MONTH('Origination_Date')) = 'YearMonth' THEN 1 ELSE 0 END 

The 'Origination_Date' field is in this format below. I tried both " " and ' ' to quote the Orgination_Date field but didn't work.

[vizAlter]

@YiC — As per your SQL expression, looks that ' Origination_Date' field has DateTime data type, correct?

And, what about 'YearMonth' field, is it a string data type?

If yes, then you need to convert the 'YearMonth' values into the Int, then compare your logic with the same data types.

If it resolves your query please mark it "Solved" or "Solved" with a Like. This will help other users find the same answer/resolution.  Thank you.

[YiC]

Field types are in line with what you said. ' Origination_Date' field has DateTime data type,  'YearMonth' field is Int64. I think this statement may not convert Origination_date field to a format that can be compared to YearMonth. Origination_date field is like this 2020-05-09 00:00:00.000

CASE WHEN (YEAR('Origination_Date')*100)+(MONTH('Origination_Date')) = 'YearMonth' THEN 1 ELSE 0 END