Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
Timestamp(Timestamp#(DateField,'YYY-MM-DDThh:mm:ss.fff'),'MM/DD/YYYY hh:mm:ss TT')
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
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]
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
I think there wont be any issue.
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
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];
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
@Daniel77 Would you be able to share the app or post your scripts?