SQL query from Oracle SQL to T-SQL

The first thing to address is the use of rownum which has no direct equivalent in TSQL but we can mimic it, and for this particular query you need to recognize that the table ALL_OBJECTS is only being used to produce a number of rows. It has no other purpose to the query.

In TSQL we can generate rows using a CTE and there are many many variants of this, but for here I suggest:

;WITH cteDigits AS ( SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) , cteTally AS ( SELECT d1s.digit + d10s.digit * 10 + d100s.digit * 100 /* add more like this as needed */ -- + d1000s.digit * 1000 /* add more like this as needed */ + 1 AS rownum FROM cteDigits d1s CROSS JOIN cteDigits d10s CROSS JOIN cteDigits d100s /* add more like this as needed */ --CROSS JOIN cteDigits d1000s /* add more like this as needed */ ) 

This will quickly spin-up 1000 rows as is and can be extended to produce many more rows by adding more cross joins. Note this returns a column called rownum which starts at 1 thus mimicking the Oracle rownum.

So next you can just add some of the remaining query, like this:

SELECT 0 run_id , 0 tran_id , 0 sort_id , ' ' tran_type , 10 prod_id , 72 type_id , 1 value , convert(varchar, dateadd(day, rownum - 1,'20160318'),121) event_publication -- several missing rows here , 0 ms FOM cteTally INNER JOIN settlement_win sw INNER JOIN prod_def pd ON sw.country = pd.country AND sw.commodity = pd.commodity WHERE pd.prod_id = 10 AND rownum <= datediff(day,'20160318','20160318') + 1 

Note that you really do not need a to_timestamp() equivalent you just need the ability to output date and time to the maximum precision of your data which appears to be to the level of seconds.

To progress further (I think) requires an understanding of the data held in the column sw.end_time. If this can be converted to the mssql datetime data type then it is just a matter of adding a number of days to that value to arrive at the event_publication and similarly if sw.end_time is converted to a datetime data type then use date_part() to get the hours, minutes and seconds from that column. e.g.

, DATEADD(day,rownum-1,CONVERT(datetime, sw.end_time)) AS event_publication 

also, if such a calculation works then it would be possible to use an apply operator to simplify the overall query, something like this

;WITH cteDigits AS ( SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) , cteTally AS ( SELECT d1s.digit + d10s.digit * 10 + d100s.digit * 100 /* add more like this as needed */ -- + d1000s.digit * 1000 /* add more like this as needed */ + 1 AS rownum FROM cteDigits d1s CROSS JOIN cteDigits d10s CROSS JOIN cteDigits d100s /* add more like this as needed */ --CROSS JOIN cteDigits d1000s /* add more like this as needed */ ) SELECT 0 run_id , 0 tran_id , 0 sort_id , ' ' tran_type , 10 prod_id , 72 type_id , 1 value , convert(varchar(23), CA.Event_publication, 121) Event_publication , datepart(day,CA.Event_publication) dd , datepart(month,CA.Event_publication) mm , datepart(year,CA.Event_publication) yyyy , datepart(hour,CA.Event_publication) hh24 , datepart(minute,CA.Event_publication) mi , datepart(second,CA.Event_publication) ss , 0 ms FOM cteTally INNER JOIN settlement_win sw INNER JOIN prod_def pd ON sw.country = pd.country AND sw.commodity = pd.commodity CROSS APPLY ( SELECT DATEADD(day,rownum-1,CONVERT(datetime, sw.end_time)) AS event_publication ) CA WHERE pd.prod_id = 10 AND rownum <= datediff(day,'20160318','20160318') + 1 

NB: IT may be necessary to include this datediff(day,'19000101,'20160318') (which equals 42445) into the calculation of the event_date e.g.

SELECT DATEADD(day,42445 + (rownum-1),CONVERT(datetime, sw.end_time)) AS event_publication 

One last point is that you could use datetime2 instead of datetime if you really do need a greater degree of time precision but there is no easily apparent requirement for that.