topic Re: Date Conversion issue in Discussions

Date Conversion issue

Phil_Nash — Sat, 10 Jun 2023 23:50:51 GMT

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

Re: Date Conversion issue

jthi — Mon, 11 Jul 2022 15:42:56 GMT

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

Re: Date Conversion issue

Phil_Nash — Mon, 11 Jul 2022 16:10:22 GMT

Thanks! That worked perfectly.

Re: Date Conversion issue

Phil_Nash — Mon, 11 Jul 2022 18:05:46 GMT

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.

Re: Date Conversion issue

jthi — Mon, 11 Jul 2022 18:13:04 GMT

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