nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Date Conversion issue

Phil_Nash

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"

Date Conversion1.PNG

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?

Date Conversion2.PNG

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

Date Conversion3.PNG

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

Thanks!

-Phil

Accepted answers

jthi

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

All comments

jthi

Numeric

Character

Back to numeric with formatting

Phil_Nash

Thanks! That worked perfectly.

Phil_Nash

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

Date Conversion4.PNG

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

Date Conversion5.PNG

jthi

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);