web analytics

Oracle: converting timestamp to date

Here is the method to display timestamp in the convenient format:

SELECT TO_CHAR (SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’) s_date FROM DUAL;

Simple conversion of the timestamp:

SELECT CAST(SYSTIMESTAMP AS DATE) x_date FROM DUAL;

Converting timestamp to char

Sometimes it’s necessary to convert Sybase timestamp to something more readable Warning! Timestamp is not date type!

declare @p timestamp declare @s varchar(16) declare @t1, @t2 varbinary(4) //— from char to timestamp @t1 = hextoint(substring(lower(@s),1,8)) @t2 = hextoint(substring(lower(@s),9,8)) @p = @t1 + @t2 //— from timestamp to char @s=(lower(convert(varchar(8),intohex(substring(p,1,4))))+ lower(convert(varchar(8),intohex(substring(p,5,4))))