Convert Numeric Date to Actual Date (YYYY-MM-DD)

[tmumaw]

Hi All,

Need a little help.  I need to convert a numeric date to the actual date.  Here is my script.  I need to find the first date and last date of services.  What am I miss?

Thanks

TempServDates:
Load
[Ship To] as ShipTo,
Min([Service_Date]) as FirstDate,
Max([Service_Date]) as LastDate
Resident VBRP_R
Group by [Ship To]
;

ServDates:
Load *,
Date(Date#(FirstDate,'YYYY-MM-DD')) as FirstServDate,
Date(Date#(LastDate,'YYYY-MM-DD'))  as LastServDate
Resident TempServDates 

[Oleg_Troyansky · Accepted solution]

Hi Thom,

assuming that Service_Date is a proper Date field (not string, not an 8-digit number like YYYYMMDD, etc...) - then min() and max() should return the numeric representation of the dates. All you need to do it to format it back into the date (there is no need to cast strings to dates using Date#):

TempServDates:
Load
[Ship To] as ShipTo,
Min([Service_Date]) as FirstDate,
Max([Service_Date]) as LastDate
Resident VBRP_R
Group by [Ship To]
;

ServDates:
Load *,
Date(FirstDate) as FirstServDate,
Date(LastDate)  as LastServDate
Resident TempServDates

cheers,

Oleg Troyansky

[swuehl]

Try

ServDates:

Load *,
Date(FirstDate,'YYYY-MM-DD')) as FirstServDate,
Date(LastDate,'YYYY-MM-DD')  as LastServDate;

Load
[Ship To] as ShipTo,
Min([Service_Date]) as FirstDate,
Max([Service_Date]) as LastDate
Resident VBRP_R
Group by [Ship To]
;

or if you don't need  to keep the numbers in separate fields (the dates will still keep the numeric representation):

ServDates:

Load
Date(Min([Service_Date]),'YYYY-MM-DD')) as FirstServDate,
Date(Max([Service_Date]),'YYYY-MM-DD')  as LastServDate,

[Ship To] as ShipTo
Resident VBRP_R
Group by [Ship To]
;

[sfatoux72]

‌

Hi,

Reading your script, I understand that the initial format of your date is 'YYYY-MM-DD´.

MIn and Max works only with numbers

Try this:

TempServDates:
Load
[Ship To] as ShipTo,
MinString([Service_Date]) as FirstDate,
MaxString([Service_Date]) as LastDate
Resident VBRP_R
Group by [Ship To]
;

ServDates:
Load *,
Date(Date#(FirstDate,'YYYY-MM-DD')) as FirstServDate,
Date(Date#(LastDate,'YYYY-MM-DD'))  as LastServDate
Resident TempServDates

[swuehl]

Maybe also have a look at

Get the Dates Right

Why don’t my dates work?

[tmumaw]

Thanks Oleg.  It worked perfectly.