Integer to date conversion

Database
Junior Supporter

Integer to date conversion

Hi,

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.

--Samir

Tags (1)
4 REPLIES
Teradata Employee

Re: Integer to date conversion

Check the logic in dbc.UsersV view definition.

Junior Contributor

Re: Integer to date conversion

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)
Junior Supporter

Re: Integer to date conversion

Hi Dieter,

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 ?

--samir

Junior Contributor

Re: Integer to date conversion

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)