Date Conversion issue

[#1]

I have a set of data from a database that represents a date in an 8-number string "CreatedDateDimID" in the screen capture.  I need it to be formatted like the date in "SterlingDimDate.Date"

I tried converting to m/d/y in the column info, but it doesn't convert it correctly (see screen capture).  What am I missing?

Gives me this result when I would expect 01/03/2022 for the first one and 04/13/2022 for the second one;

I need to be able to capture the delta between the two dates.

Thanks!

-Phil

[#2 · Accepted solution]

I think you might have to change the column to Character column, press apply (and possible even OK), then go back to column properties, set column to numeric continuous, set correct format and then press apply.

Numeric

Character

Back to numeric with formatting

[#3]

Thanks! That worked perfectly.

[#4]

I'm trying to build this into JSL and I can't seem to get the last step to work..

It doesn't seem to respect the Input Format.. I'm certain my code is wrong.

[#5]

If I were to do this with scripting I would use different method. One option would be to use Informat:

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(1),
	New Column("Col", Numeric, "Continuous", Format("Best", 12), Set Values([20220103]))
);
wait(1);
Column(dt, "Col") << Set Each Value(Informat(Char(:Col), "YYYYMMDD"));
wait(1);
Column(dt, "Col") << Format("m/d/y", 12);