I have date in dbc.oldpasswords which is 735960 and 736051. I want to convert it to a date field so that i can read it. The format of this column is - integer format '_,__,__,_9'. I have tried to convert it the way we convert an integer date field to a date fied, but i get error that invalid date provided.
I don't know who wrote those conversions originally, but it's just a slightly modified version of a astronomical julian day calculation, so you can use
DATE '0001-01-01' + (PasswordDate-307)
Thanks, this converts the interger date to DATE. What are the figures, 0001-01-01 amd 307 ? Do i need to change it according to my environment ?
No, PasswordDate seems to be the number of days since a start date and this start date is 307 days before Teradata's minimum date 0001-01-01.
You could also use an adjusted state date like
DATE '2000-01-01' + (PasswordChgDate-730426)