Date and Time format

[cmutombo]

Hi Community

Please help

This below date :

2021-08-14 00:00:00.000000 is at which format ?? 

If I want to explain it to format  : '/M/DD/YYYY' for ex

[Taoufiq_Zarra]

@cmutombo  if you want this format : /8/14/2021

first on top you can change DateFormat to SET DateFormat='/M/DD/YYYY';

then if you you can use Date#() to transforme you date

if you have a problem with Date# you can use this version:

makedate(subfield(Date,'-',1),subfield(Date,'-',2),left(subfield(Date,'-',3),2))

like this :

LOAD *,makedate(subfield(Date,'-',1),subfield(Date,'-',2),left(subfield(Date,'-',3),2)) as New_Date INLINE [
Date
2021-08-14 00:00:00.000000
] 

output:

or with Month() and Year(),...

[cmutombo]

Hi Taoufiq_Zarra

Thanks for your quick reply.

I've try your solution, Thanks ! 

But,  I am using many formulas, and I want the easy one. I would like some thing like  : 

=date(date#(max([GROSS_ADDS.DATE_],'YYYY-MM-DD h:mm:ss[.fff] TT') ,'M/DD/YYYY')

The issue is : I don't know format of my dimension max([GROSS_ADDS.DATE_].

In a table it appear : 2021-08-15 00:00:00.000000, I want to change it to 08/15/2021

because i will use it one a SUMIF formula after.

PREVIEW

[Taoufiq_Zarra]

Maye be you can use it like

=date(max(floor(Timestamp#([GROSS_ADDS.DATE_],'YYYY-MM-DD h:mm:ss[.fff] TT'))) ,'M/DD/YYYY')

or you can share a sample data to check

[cmutombo]

=date(max(floor(Timestamp#([GROSS_ADDS.DATE_],'YYYY-MM-DD h:mm:ss[.fff] TT'))) ,'M/DD/YYYY')

 give No result...

[cmutombo]

Let me tell you the story.

I come from this formula : 

=sum({<[GROSS_ADDS.DATE_]={"$(=max([GROSS_ADDS.DATE_]))"}>}[GROSS_ADDS.SUBS]) IS WORK CORRECTY

Now I want to take the day before max([GROSS_ADDS.DATE_], Kind of Date(max([GROSS_ADDS.DATE_]-1)

The issue is Date(max([GROSS_ADDS.DATE_]-1) give : 08/15/2021 then, my sum is blank because [GROSS_ADDS.DATE_] is at format 2021-08-14 00:00:00.000000

[cmutombo]

Let me tell you where I come from :

I used this formula :

1. =sum({<[GROSS_ADDS.DATE_]={"$(=max([GROSS_ADDS.DATE_]))"}>}[GROSS_ADDS.SUBS]).. Work CORRECTLY

2. I want same SUM at they before max([GROSS_ADDS.DATE_], then Date(max([GROSS_ADDS.DATE_]-1)

3. The issue is Date(max([GROSS_ADDS.DATE_]-1) is out on format 8/14/2021 and [GROSS_ADDS.DATE_] is at format 2021-08-14 00:00:00.000000

4. How to convert Date(max([GROSS_ADDS.DATE_]-1) at format : 2021-08-14 00:00:00.000000

[MayilVahanan]

Hi @cmutombo 

Based on ur sample, max([GROSS_ADDS.DATE_]) <- returns " 2021-08-15 00:00:00.000000", 

so try like below

Date(Date#(Left(max([GROSS_ADDS.DATE_]), 10),'YYYY-MM-DD'), 'MM/DD/YYYY')

or

Date(Left(max([GROSS_ADDS.DATE_]), 10), 'MM/DD/YYYY')

[cmutombo]

Hi MayilVahanan

YOU ARE RIGHT ! Thanks a lot

[MayilVahanan]

Hi

Please close the thread and its helpful for others to refer the right answer in future for same kind of scenario.