Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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(),...
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
=date(max(floor(Timestamp#([GROSS_ADDS.DATE_],'YYYY-MM-DD h:mm:ss[.fff] TT'))) ,'M/DD/YYYY')
give No result...
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
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
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')
Hi MayilVahanan
YOU ARE RIGHT ! Thanks a lot
Hi
Please close the thread and its helpful for others to refer the right answer in future for same kind of scenario.