convert query from sql to oracle

Question

I have the following SQL query:

SELECT
@weekenddtm = CONVERT(VARCHAR,DATEADD(DD,(7 - DATEPART(DW,@p_end_dtm)),@p_end_dtm),111)

and I tried converting it into oracle using this query:

v_weekenddtm   := CAST(p_end_dtm + NUMTODSINTERVAL((7-TO_NUMBER(TO_CHAR(p_end_dtm,'D'))),'DAY')  AS DATE);

,but it is giving me error. Any idea how to go ahead?

How do you want to "convert from sql" ?? Oracle - like all major RDBMS - uses SQL, too. SQL is the data query and data definition language for all major database systems...... — marc_s
– marc_s, Commented Jul 9, 2010 at 7:57
If you post code or XML, please highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Jul 9, 2010 at 7:58
Also, rather than "an error" it woul dhelp if you copied and pasted the error message. — Tony Andrews
– Tony Andrews, Commented Jul 9, 2010 at 8:27
"from SQL to Oracle" does not make sense. Oracle uses SQL as their its language - just like every other relational database. You should also explain what the (presumably) T-SQL code is doing for those that know Oracle but not SQL Server. — user330315
– user330315, Commented Nov 23, 2016 at 8:53

Tony Andrews · Accepted Answer · 2010-07-09 09:55:13Z

1

What are the datatypes of p_end_dtm and v_weekend_dtm? Your code works if they are as follows:

declare
   p_end_dtm timestamp;
   v_weekend_dtm date;
begin
   v_weekend_dtm := CAST(p_end_dtm+ NUMTODSINTERVAL((7-TO_NUMBER(TO_CHAR(p_end_dtm,'D'))),'DAY')  AS DATE);
end;

answered Jul 9, 2010 at 9:55

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

convert query from sql to oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related