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;

Oracle: convert LONG to VARCHAR2

SET serveroutput ON DECLARE s_sql VARCHAR2(2000); s_rez VARCHAR2(32767); BEGIN s_sql := ‘select LONG_COLUMN from SOME_TABLE where ID_COLUMN = 123’ ; EXECUTE IMMEDIATE s_sql INTO s_rez; — print it on the screen or convert/transform/search… dbms_output.put_line ( s_rez ) ; END;

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