change this date format to DD/MM/YYY

[Dolly123]

[stevedark]

Hi @Dolly123 

I presume that this date is in a text file, such as a CSV?

You will want something like:

Date(Date#(replace(replace(replace(replace(order_date, 'nd ', ' '), 'st ', ' '), 'rd ', ' '), 'th ', ' '), 'DD MMM YYYY'), 'DD/MM/YYYY') as [Order Date],

Another way would be to use the mid function to pick out the parts of the string, but it would need to deal with one and two digit days, so would be slightly cumbersome there.

Hope that helps.

Steve

https://www.quickintelligence.co.uk/blog/

[Dolly123]

see this no output expect 1

[stevedark]

Hi @Dolly123 

It seems strange it would only work on that one date. Are there leading or trailing spaces in the text?

Perhaps try:

Date(Date#(replace(replace(replace(replace(trim(order_date), 'nd ', ' '), 'st ', ' '), 'rd ', ' '), 'th ', ' '), 'DD MMM YYYY'), 'DD/MM/YYYY') as [Order Date],

Steve

[Dolly123]

still not working

[stevedark]

Hi @Dolly123 

Can you share the app that you are working on (with sensitive data removed) or the file you are trying to load?

The code definitely works in certain contexts. I have just tried with this inline load:

DateFormat:
LOAD
order_date,
Date(Date#(replace(replace(replace(replace(trim(order_date), 'nd ', ' '), 'st ', ' '), 'rd ', ' '), 'th ', ' '), 'DD MMM YYYY'), 'DD/MM/YYYY') as [Order Date]
INLINE [
order_date
1st Jan 2023
  2nd Jan 2023
3rd Jan 2023  
    4th Jan 2023
5th Jan 2023
];

That gives the following:

I presume you are dealing with this in the load script, and not trying to do it in the table? You need to use an Aggr statement to do it in the table, but I would really not recommend that.

Steve

[Dolly123]

check this file let me know

half date and half se text date i need in 1 column date

[Dolly123]

your code working on front end only 

[stevedark]

Hi @Dolly123 

The original data is not in the format you mention, but there are some bits of nonsense in the file which you will need to get fixed.

I suspect you have formatting on, most likely in the set statement in the load script. Find that set statement and change it like this:

SET DateFormat='DD/MM/YYYY';

You can also format the date in the load script. You don't need the replaces or the date#, just do this:

LOAD
order_date as original_date,
Date(order_date, 'DD/MM/YYYY') as order_date
FROM [lib://DL/Book1.xlsx]
(ooxml, embedded labels);

The problem you have is where the dates are not valid in the Excel file.

You can check for valid dates in the load, like this:

if(isnum(order_date), 'Yes', 'No') as Valid

Looking at the kind of results you get where it is not valid:

It's a bit of a mess. You will need to try different formats until you get all valid dates:

LOAD
*,
if(isnum(order_date), 'Yes', 'No') as Valid
;
LOAD
order_date as original_date,
Date(coalesce(
    order_date*1,
    Date#(order_date, 'MMMM DD, YYYY'),
    Date#(order_date, 'DD MMM YYYY')), 'DD/MM/YYYY') as order_date
FROM [lib://DL/Book1.xlsx]
(ooxml, embedded labels);

The coalesce function tries each parameter in turn until it finds a valid one. Multiplying the date by 1 makes any non valid dates null, so that the other conversion factors are tried.

The best thing you can do though is to get someone to sort your source data out. You can obviously use Qlik to point out where it is wrong though.

Hope that helps,

Steve

[Dolly123]

I have try your  last code not working