Convert Date format

[Daniel77]

Hi,

I am familiar with converting dates but have a different format i have not see which i need to convert, i have tried the following but unsuccessfully.

Timestamp(Date#( 'YYYY-MM-DDThh:mm:ss.sss'), 'MM/DD/YYYY hh:mm:ss TT') as [OrderDate],

Can anyone assist with converting 

2022-11-12T10:58:46.193

to 

2/23/2023 12:10:36 AM

Thank you

Daniel

[BrunPierre]

Timestamp(Timestamp#(DateField,'YYY-MM-DDThh:mm:ss.fff'),'MM/DD/YYYY hh:mm:ss TT')

[Daniel77]

Hi Brun, 

thank yo for the quick response, 

I have tried this.

Timestamp(Timestamp#([OrderDate],'YYY-MM-DDThh:mm:ss.fff'),'MM/DD/YYYY hh:mm:ss TT')as [OrderDate],

and the original date is still returned, any ideas?

original date:         2022-11-12T10:58:46.193

after the import: 2022-11-12T10:58:46.193

Thanks so much

Daniel

[Chanty4u]

Try this 

Timestamp(Date#('2022-11-12T10:58:46.193', 'YYYY-MM-DDThh:mm:ss.sss'), 'M/D/YYYY h:mm:ss TT') as [OrderDate]

[Daniel77]

Thanks Chanty,

I have tried this and the result is a null value:

Timestamp(Date#('2022-11-12T10:58:46.193', 'YYYY-MM-DDThh:mm:ss.sss'), 'M/D/YYYY h:mm:ss TT') as [OrderDate],

Thanks again for your help. 

Would it be an issue i am loading from a JSON API?

Thanks

[Chanty4u]

I think there wont be any issue.

[Daniel77]

I checked the Set Dates and its like this:

SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

Should this be changed for the date conversion to work?

Thanks

Daniel

[BrunPierre · Accepted solution]

LOAD *,
Timestamp(Timestamp#(OrderDate,'YYY-MM-DDThh:mm:ss.fff'),'MM/DD/YYYY hh:mm:ss TT') as [Converted Order Date]

Inline [
OrderDate
2022-11-12T10:58:46.193];

[Daniel77]

Hi,

I can see this is working for you, thank you so much for taking the time to look at this.

Can i ask, are your setting like this? or did you change?

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Thanks Brun.

Daniel

[BrunPierre]

@Daniel77 Would you be able to share the app or post your scripts?