2

This question has been asked several times on this forum. But I did not find a solution to this problem

select dateadd(ms, CAST (1430607256000 AS BIGINT), '1970-01-01 00:00:00.0');

I am trying to get this in human readable format. But I am getting the below error:

[22003][FreeTDS][SQL Server]Arithmetic overflow error converting expression to data type int.

I actually need the dates which is in between current_date-3 00:00:00 and current_date-3 23:59:59.

I am an Oracle person, new to SQL Server. Please help.

My Oracle query is:

SELECT TRIM(datecol)  
FROM transactions 
WHERE datecol BETWEEN (((TO_DATE(TO_CHAR(CURRENT_DATE+INTERVAL '-3'DAY, 'DD MON YYYY')||' '||'00:00:00','DD MON YYYY HH24:MI:SS') - TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400000) + 14400000) AND (((TO_DATE(TO_CHAR(CURRENT_DATE+INTERVAL '-3'DAY, 'DD MON YYYY')||' '||'23:59:59','DD MON YYYY HH24:MI:SS') - TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400000) + 14400000);
Binayak Chatterjee
  • 51
  • 1
  • 8

1 Answers1

5

Try this:

First Change the MilliSeconds to Minutes(1430607256000/60000) and the Add it to the Date Value.

SELECT DATEADD(MINUTE,1430607256000/60000,'1970-01-01 00:00:00.0');

OutPut:

2015-05-02 22:54:00.000
DineshDB
  • 5,998
  • 7
  • 33
  • 49