How to convert date funtions from T-SQL to SQL (Oracle)?

For a non T-SQL guy, what do those do?

I'm going to answer with "not much", well "not much" that I comprehend.

First it's important to note that a "timestamp" data type has nothing whatever to do with dates or times in T-SQL (yep, that's right it's a "rowversion") so I can only assume d.SESSION_TIMESTAMP is a datetime or datetime2 data type (in Oracle that would be a timestamp).

Here are some simulated results, but note I have substituted a variable in place of that field with that variable set to a system value 'current_timestamp'*

**Query 1**:

    declare @SESSION_TIMESTAMP as datetime
    set @SESSION_TIMESTAMP = CURRENT_TIMESTAMP
    
    SELECT
      getdate()
    , getutcdate()
    , @SESSION_TIMESTAMP
    
    , CAST(CONVERT(char(10) ,dateadd(SECOND,datediff(SECOND,getutcdate(),getdate()),@SESSION_TIMESTAMP),101) AS datetime)
    
    , Datepart(dayofyear,CAST(CONVERT(char(10) ,dateadd(SECOND,datediff(SECOND,getutcdate(),getdate()),@SESSION_TIMESTAMP),101) AS datetime))
    	
    	
    

**[Results][2]**:
    
    |                       COLUMN_0 |                       COLUMN_1 |                       COLUMN_2 |                       COLUMN_3 | COLUMN_4 |
    |--------------------------------|--------------------------------|--------------------------------|--------------------------------|----------|
    | October, 22 2013 02:22:13+0000 | October, 22 2013 02:22:13+0000 | October, 22 2013 02:22:13+0000 | October, 22 2013 00:00:00+0000 |      295 |



  [1]: http://sqlfiddle.com/#!3/d41d8/23231

Select allOpen in new window

* more "timestamp" confusion I'm afraid

here's a better interpretation of the T-SQL I think:

DECLARE @SESSION_TIMESTAMP AS datetime
SET @SESSION_TIMESTAMP = CURRENT_TIMESTAMP

SELECT
  getdate()             AS is_sysdate
, getutcdate()          AS is_sysdate_sort_of
, @SESSION_TIMESTAMP    AS is_a_column_value

, datediff(SECOND,getutcdate(),getdate()) AS tz_offset_in_secs

, dateadd(SECOND,datediff(SECOND,getutcdate(),getdate()),@SESSION_TIMESTAMP)  AS add_tz_offset_to_field

, CONVERT(char(10) ,dateadd(SECOND,datediff(SECOND,getutcdate(),getdate()),@SESSION_TIMESTAMP),101)    AS trunc_that

, CAST(CONVERT(char(10) ,dateadd(SECOND,datediff(SECOND,getutcdate(),getdate()),@SESSION_TIMESTAMP),101) AS datetime) AS but_make_it_timestamp

, Datepart(dayofyear,CAST(CONVERT(char(10) ,dateadd(SECOND,datediff(SECOND,getutcdate(),getdate()),@SESSION_TIMESTAMP),101) AS datetime)) AS day_of_year
    
    
|          IS_SYSDATE      |       IS_SYSDATE_SORT_OF |        IS_A_COLUMN_VALUE | TZ_OFFSET_IN_SECS |   ADD_TZ_OFFSET_TO_FIELD | TRUNC_THAT |    BUT_MAKE_IT_TIMESTAMP | DAY_OF_YEAR |
|--------------------------|--------------------------|--------------------------|-------------------|--------------------------|------------|--------------------------|-------------|
| 2013-10-22 02:59:57+0000 | 2013-10-22 02:59:57+0000 | 2013-10-22 02:59:57+0000 |                 0 | 2013-10-22 02:59:57+0000 | 10/22/2013 | 2013-10-22 00:00:00+0000 |         295 |


 http://sqlfiddle.com/#!3/d41d8/23237
 

Select allOpen in new window

Mmmm, there may very well be an easier way to get a numeric "UTC Offset" for altering that field, but the only way I know of is to use tz_offset which returns a varchar, so I "think" the equivalent to the original TSQL code is this:

----------

CAST(CONVERT(char(10) ,dateadd(second,datediff(second,getutcdate(),getdate()),d.SESSION_TIMESTAMP),101) AS datetime)

TRUNC(d.SESSION_TIMESTAMP + (SIGN(TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 1, 3)))* (TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 2, 2)) * 60 +  TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 5, 2))  ) / 1440))

----------

Datepart(dayofyear,CAST(CONVERT(char(10) ,dateadd(second,datediff(second,getutcdate(),getdate()),d.SESSION_TIMESTAMP),101) AS datetime))

TO_CHAR(TRUNC(d.SESSION_TIMESTAMP + (SIGN(TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 1, 3)))* (TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 2, 2)) * 60 +  TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 5, 2))  ) / 1440)))
TO_NUMBER(TO_CHAR(TRUNC(d.SESSION_TIMESTAMP + (SIGN(TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 1, 3)))* (TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 2, 2)) * 60 +  TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 5, 2))  ) / 1440)),'DDD'))

----------

*/


select
      SIGN(TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 1, 3)))
          * (
                TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 2, 2)) * 60
             +  TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 5, 2))
            ) / 1440
       utc_offset_minutes

    , SIGN(TO_NUMBER(SUBSTR('-04:30', 1, 3)))
          * (
                TO_NUMBER(SUBSTR('-04:30', 2, 2)) * 60
             +  TO_NUMBER(SUBSTR('-04:30', 5, 2))
            ) / 1440
       eg_utc_offset_minutes
from dual
;



SELECT
      sysdate                                                      getdate_equiv
    , SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)                           getutcdate_equiv
    , SIGN(TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 1, 3)))
          * (
                TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 2, 2)) * 60
             +  TO_NUMBER(SUBSTR(TZ_OFFSET('UTC'), 5, 2))
            ) / 1440                                               UTC_offset_mins
    , TRUNC(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP))                    UTC_truncated
    , TO_CHAR(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),'DDD')            day_of_year_char
    , TO_NUMBER(TO_CHAR(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),'DDD')) day_of_year_num
FROM dual
;

http://sqlfiddle.com/#!4/d41d8/19357

Select allOpen in new window

if my logic is correct on tz_offset (converting it to minutes) then maybe making it a function could be worth pursuing.

Rather than providing the T-SQL syntax, can you just show what the sample data (and the datatype) looks like originally and what it looks like after the T_SQL functions are applied?

Here are simulated results,

let's say session_timestamp = '2013-04-05 00:06:04.000'

select
'2013-04-05 00:06:04.000' session_timestamp,
getutcdate(),getdate(),
datediff(second,getutcdate(),getdate()) date_diff,
dateadd(second,datediff(second,getutcdate(),getdate()),'2013-04-05 00:06:04.000') date_add,
CONVERT(char(10) ,dateadd(second,datediff(second,getutcdate(),getdate()),'2013-04-05 00:06:04.000'),101) event_date,
CAST(CONVERT(char(10) ,dateadd(second,datediff(second,getutcdate(),getdate()),'2013-04-05 00:06:04.000'),101)
AS datetime) time_stamp,
Datepart(dayofyear,CAST(CONVERT(char(10),
dateadd(second,datediff(second,getutcdate(),getdate()),'2013-04-05 00:06:04.000'),101) AS datetime)) julian_date

----Above query returns below data

session_timestamp: 2013-04-05 00:06:04.000
getutcdate(): 2013-10-22 17:38:56.013
getdate(): 2013-10-22 12:38:56.013      
date_diff: -18000      
date_add: 2013-04-04 19:06:04.000      
event_date: 04/04/2013      
time_stamp: 2013-04-04 00:00:00.000      
julian_date: 94

It looks like, the above query is subtracting 5 hours from "session_timestamp" to find out "event_date" and "julian_date".

How do I accomplish this using sql (in Oracle)?

"first code line of question (indented) is:
CAST(
     CONVERT(char(10) ,
                       dateadd(second,
                                      datediff(second,getutcdate(),getdate())
                      ,d.SESSION_TIMESTAMP)  --<< inside dateadd, must be a date*
     ,101)
AS datetime)"

--<< inside dateadd, must be a date*

Answer is no.

session_timestamp is a character string.

Understood your comments. But seems like date (second) can be added to a character string in SQL Server. I know it doesn't sound right. But if you run below query in SQL Server then you won't get any syntax error.

select '2013-04-05 00:06:04.000' session_timestamp,
datediff(second,getutcdate(),getdate()),
dateadd(second,datediff(second,getutcdate(),getdate()),'2013-04-05 00:06:04.000')

That's not the case in Oracle though, character string must need to be converted to date before you can add/subtract hours from that timestamp. For example,

select trunc(to_date(substr(trim('2013-04-05 00:06:04.000'),1,19),'yyyy-mm-dd hh24:mi:ss') - numtodsinterval(5,'hour')) event_date

>> But seems like date (second) can be added to a character string in SQL Server.
This is true and perhaps my statement is a bit misleading - for SQL Server (mssql)

mssql is quite tolerant of "implicit conversions" and as long as that string can be evaluated to a valid date/time - it will do it for you. (but there are some risks in this*)

Oracle is NOT tolerant of implicit conversions, and it requires you to provide the necessary data type in a function. 3 things you will definitely need in Oracle:
to_char()
to_number()
to_date()

What I had hoped to convey was that if your input is a string then you have conversions to perform.

----
* "ambiguous date strings"
12/08/2013
what date is that?

hint, to me it's the 12th of August

My solution can be accted as multiple solutions.