I'm trying to migrate my sql query to oracle but it seems That i cannot convert my query due to DateAdd function.
STRINGVARIABLE = '1361439468476'
output is : Feb 20,2013
convert (char(12), (dateadd(s, convert(bigint, STRINGVARIABLE) / 1000, convert(datetime, '1-1-1970 00:00:00'))), 107)
DATEADD doesn't exist in Oracle. There are various ways to manipulate dates, but this is fairly straightforward:
select date '1970-01-01' + (to_number('1361439468476') / (1000*60*60*24))
from dual;
DATE'1970
---------
21-FEB-13
... which is actually 21/02/2013 09:37:48, so not sure why you have it as 20-FEB-13.
If you want to keep the millisecond precision you can use a TIMESTAMP instead:
select timestamp '1970-01-01 00:00:00'
+ numtodsinterval(to_number('1361439468476')/1000, 'SECOND')
from dual;
TIMESTAMP'1970-01-0100:00:00'+NUMTODSINTERVAL(TO_NUMBER('1361439468476')/10
---------------------------------------------------------------------------
21-FEB-13 09.37.48.476000000
I'm not sure what the 107 in your query is doing though, or convert, perhaps they are formatting the result as a string?
OK, I see what convert(..., 107) is doing; the equivalent is:
select to_char(date '1970-01-01'
+ (to_number('1361439468476') / (1000*60*60*24)), 'Mon DD, YYYY') as dt
from dual;
DT
------------
Feb 21, 2013
... using to_char() with a Mon DD, YYYY format model.
