Convert Julian date

[bigdataxhi]

Hi all,

I hope you can help with this quick query.

In my data I have several date fields. The format of the dates is (from what I can see) "Julian" dates set as 7 digits:

YYYY[day of the year in 3 characters]

Samples:

Date_of_Birth

1965166
1970259
1965063

Please can you help with a load script for this Date_of_Birth field, converting the above to "normal" dates in DD/MM/YYYY?

Many thanks!

[Anil_Babu_Samineni · Accepted solution]

What does the date you are expecting for this? 1965166

May be this?

=TimeStamp( MakeDate( Floor(Date_of_Birth / 1000) ) + 1000 * Frac(Date_of_Birth / 1000) -1 )

Or

=Timestamp(Date_of_Birth /24/60/60s

[sunny_talwar]

May be like this?

Table:

LOAD Date_of_Birth,

  Left(Date_of_Birth, 4) as Year,

  Right(Date_of_Birth, 3) as Days;

LOAD * INLINE [

    Date_of_Birth

    1965166

    1970259

    1965063

    1965001

];

Left Join (Table)

LOAD Date_of_Birth,

  IterNo() as Days,

  Date(MakeDate(Year) + IterNo()-1) as Date

Resident Table

While YearStart(MakeDate(Year)) + IterNo() <= YearEnd(MakeDate(Year));

[sunny_talwar]

Wow this is much simpler

[bigdataxhi]

Perfect, thanks!