Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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));
Wow this is much simpler
Perfect, thanks!