Date Format Changes

[gireesh1216]

How to change number format to date format?

Example Number:-693593

I am using DATE(-693593  ) function.getting 01-01-0001.

how to convert  -693593  to  01-01-2001 ????

[tresesco]

Do you have a field with such many negative values? If yes, please post few more such values and expected output dates against them, so that we could figure out a formula.

[gireesh1216]

Hi please see last replay...

[gireesh1216]

No...I am getting only one negative value that is (-693593) only...

[tresesco · Accepted solution]

Then you may try a static fix like:

Date(If( [Termination Date]<0, MakeDate(2001,1,1), [Termination Date])) as [Termination Date]

[gireesh1216]

This expression is true?

=date(if(Termination_date='-693593','36892',Termination_date))

please help me

[vcanale]

Hi,
if you test it in a text object
=date('01-01-01') returns 01/01/2001
=date('01-JAN-01') returns '-'
with an INLINE tbl or something else try to find a way to convert JAN to 01
check also the SET DateFormat in your script

[Anil_Babu_Samineni]

Yes, this is true this will give 01-01-2001 only. But, tresco solution is bit make sense

Try over there

[tresesco]

I would prefer a numeric comparison (removing quotes) like:

date(if(Termination_date=-693593 ,36892,Termination_date))

[rwunderlich]

‌MIguel,

I'm not sure what this particular date number represents, but date numbers can be negative for dates prior to Dec 31, 1899.

Rob

[Miguel_Angel_Baeyens]

Rob,

I should have been more specific, while technically they can be whatever length and sign, I don't think you ever analyze anything prior to 1899, so if you are getting negative numbers for dates that implies either a zero date (whatever is in the source system) or bad formatting.

I'll correct my prior post with this regard.

Miguel

EDIT: Actually, 01/01/0001 is a valid date. However, I hesitate you have anything which happened on 01/01/0001.