
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Convert Date format
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Timestamp(Timestamp#(DateField,'YYY-MM-DDThh:mm:ss.fff'),'MM/DD/YYYY hh:mm:ss TT')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think there wont be any issue.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Daniel77 Would you be able to share the app or post your scripts?

- « Previous Replies
-
- 1
- 2
- Next Replies »