Converting to dat

[jvaneert]

Hello,

I am querying a database with a period column.
The periods are stored as following : YYMM

So november 1998 is "9811"
So september 2000 is "9"
So oktober 2000 is "10"
So march 2003 is "403"
So november 04 is "411"

Could someone provide me with an impromptu formula which converts these periods to the first date of the month in format DD-MM-YY

So "9811" should be 1-11-98
So "403" should be 1-3-04

Best regards,

Jules

 

[GJParker]

Create 3 formulas

Month
If (Period <=12) Then ((Period)Else(string-to-integer(right(number-to-string(Period),2)))

Year
if ( Period <= 12) Then (2000 )else if ( Period >= 9000 ) Then ( 1900 + string-to-number(left(number-to-string( Period ),2))) else ( 2000 + integer-divide( Period,100))

RealDate
'01-' + number-to-string-padded( Month , 2) + '-' + number-to-string( Year )

Replace Period with your own field

HTH

Gary Parker
MIS Data Analyst
Manchester, England