How to convert Julian Date with YYYYDDD format to YYYY-MM-DD format dynamically using SQL

[Bhaskargvb]

Hi

Please help me with the formatting of date - Julian Date - YYYYDDD to YYYY-MM-DD

2034104 - YYYYDDD

TO

2034-04-14 - YYYY-MM-DD

Thanks in Advance

[LDR]

Hi @Bhaskargvb 

Using the Script Editor maybe a solution like the following could help you:

dates:
Load Date(Floor(FirstDate) + Days,'YYYY-MM-DD') AS Final_Date,
Dates
;
Load Date(Left(Dates,4) & '-01-01','YYYY-MM-DD') As FirstDate,
Num(Right(Dates,3)) as Days,
Dates
INLINE [
Dates
2023300
2023301
];

Regards

[BrunPierre]

Try:

Date(TimeStamp( MakeDate( Floor(DateField/ 1000) ) + 1000 * Frac(DateField/ 1000) -1 ),'YYYY-MM-DD')

[LDR]

Hi @BrunPierre ,

Umm  you're right!!! jajaja

In my solution I forgot to substract 1 however, I prefer yours.

Regards