How to convert numbers like 1217508668 to date and time

[magdy]

I want to convert numbers like 1217508668 to date and time

[ck9663]

Your question is too generic. In any case these are sql server function to convert value/columns into a different data type.

Happy Coding!!!

~~ CK

[magdy]

Thank you too much, your fast reply,, OK, Look, I've a sql database table contains field named open_date all records shown are like 10 digits which appeared in the htmpl pages like mm/dd hh:mm.. So i want extract a report that can display these digits to be like mm/dd hh:mm....

Thank you in advance your tries,,

[ck9663]

... And how would you convert the number on your title?

1217508668 = what date?


~~ CK

[code green]

Is this a unix timestamp by any chance?

[magdy]

No its SQL

[gpl]

If it was a unix time, it evaluates to 31/07/2008 12:51pm
Is this how it appears on your html page ?
What algorithm does the page use to calculate the display value ?

[magdy]

Yes.... Marvellous... please, provide me with the sql code that i can put in the crystal report program to extract a reports....

[magdy]

But the format is mm/dd/yyyy hh:mm pm or am

[code green]

If it is a unix timestamp you need to add 1/1/1970 to get the date.
Then use CONVERT to format the date

SELECT CONVERT(VARCHAR(DATEADD(SS,{1217508668},'19700101'),109))

I think this will convert your timestamp to mm/dd/yyyy hh:mm pm format.

But this is a string format.
To get to SQL Server format CAST AS DATETIME is needed

Not sure about Crystal Reports