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

What would be the equivalent syntax of below T-SQL in Oracle?

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

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

In a above examples, session_timestamp could be any timestamp during the day, for example,

'2013-09-01 01:30:40.000'
'2013-09-20 18:30:40.000'
'2013-09-07 06:45:40.000'
IT_ETLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
For a non T-SQL guy, what do those do?
0
PortletPaulfreelancerCommented:
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

Open in new window

* more "timestamp" confusion I'm afraid
0
PortletPaulfreelancerCommented:
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
 

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PortletPaulfreelancerCommented:
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

Open 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.
0
awking00Commented:
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?
0
IT_ETLAuthor Commented:
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)?
0
IT_ETLAuthor Commented:
I think below query should work,

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,
to_char(to_date(substr(trim('2013-04-05 00:06:04.000'),1,19),'yyyy-mm-dd hh24:mi:ss') - numtodsinterval(5,'hour'),'ddd') partition_day
from dual

Any suggestions??
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
If you are content with stipulating the wanted timezone offset as 5 hours, then I think this may be closer to the original:
CREATE TABLE TABLE_D
    (SESSION_TIMESTAMP timestamp)
;

INSERT ALL 
    INTO TABLE_D (SESSION_TIMESTAMP)
         VALUES ('05-Apr-2013 12:06:04 AM')
SELECT * FROM dual
;


SELECT
      trunc(d.SESSION_TIMESTAMP - numtodsinterval(5,'hour')) AS event_date
    , to_number(to_char(trunc(d.SESSION_TIMESTAMP) - numtodsinterval(5,'hour'),'DDD')) AS partition_day
FROM table_d d
    

**[Results]**:

|                   EVENT_DATE | PARTITION_DAY |
|------------------------------|---------------|
| April, 04 2013 00:00:00+0000 |            94 |



: http://sqlfiddle.com/#!4/ec1a5/12 

Open in new window

i.e.
In your sample immediately before it assumes session_timestamp is a string, but:

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)

& d.SESSION_TIMESTAMP should be a datetime (or date,smalldatime, datetime2)
*OR there is an implicit conversion being performed

and the result is also datetime

the second code line of question returns an integer (from datepart)
0
IT_ETLAuthor Commented:
"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
0
PortletPaulfreelancerCommented:
>> 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
0
IT_ETLAuthor Commented:
My solution can be accted as multiple solutions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.