oracle to migration - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

oracle to migration

Author

Topic

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-11-14 : 07:49:21

SELECT
to_char(Month, 'Month - YYYY') mText,
to_char(Month, 'dd-Mon-yyyy') MVALUE
FROM
(
SELECT add_months (trunc (to_date(to_char(p_startday, 'MM/DD/YYYY'),'MM/DD/YYYY'), 'MM'), 1*Level -1)
Month FROM Dual
CONNECT BY Level <= MONTHS_BETWEEN(to_date(to_char(p_endday, 'MM/DD/YYYY'),'MM/DD/YYYY'),
to_date(to_char(p_startday, 'MM/DD/YYYY'),'MM/DD/YYYY')) + 1
order by month
);

I have to migrate above code to SQL Server 2008, but I have problem with CONNECT BY Level.. I'm not getting that.. is it same as CONNECT BY PRIOR and also how to change month into 'Month - YYYY' this format

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-14 : 08:55:12

Do you understand what CONNECT BY Level does? (I don't!) Can you explain what it does with examples? Then we can fashion a T-SQL equivalent.

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-11-17 : 07:07:22

connect by used for recursion

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 08:59:10

Oh, then in SQL Serer terms, you'd probably use a recursive CTE. however, just knowing Connect By is used for recursion doesn't help me write the code. I need to know (with examples), what happens when the query with CONNECT BY runs.

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-11-25 : 04:52:15

WITH
h$cte AS
(
SELECT 1 AS LEVEL
UNION ALL
SELECT h$cte.LEVEL + 1 AS LEVEL
FROM h$cte
WHERE LEVEL <= sysdb.ssma_oracle.months_between(CONVERT(datetime2, CONVERT(varchar(max), @p_endday, 101), 101),
CONVERT(datetime2, CONVERT(varchar(max), @p_startday, 101), 101)) + 1
)

SELECT sysdb.ssma_oracle.to_char_date(fci.MONTH, 'Month - YYYY') AS mText, sysdb.ssma_oracle.to_char_date(fci.MONTH, 'dd-Mon-yyyy') AS MVALUE
FROM
(
SELECT TOP 9223372036854775807 dateadd(m, 1 * h$cte.LEVEL - 1,
sysdb.ssma_oracle.trunc_date2(CONVERT(datetime2, CONVERT(varchar(max),
@p_startday, 101), 101), 'MM')) AS MONTH
FROM h$cte
ORDER BY MONTH
) AS fci

is it ok

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-11-25 : 05:20:16

Oracle Output

P_MONTH_LIST
----------------------------
MTEXT MVALUE
---------------- -----------
May - 2014 01-May-2014
June - 2014 01-Jun-2014
July - 2014 01-Jul-2014
August - 2014 01-Aug-2014
September - 2014 01-Sep-2014
October - 2014 01-Oct-2014

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 09:12:17

That's nice, but what is the input data that produces this output when run through the query?

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-11-26 : 00:24:01

Oracle Input

p_startday = 10/05/2014
p_endday = 14/10/2014

Oracle Output

P_MONTH_LIST
----------------------------
MTEXT MVALUE
---------------- -----------
May - 2014 01-May-2014
June - 2014 01-Jun-2014
July - 2014 01-Jul-2014
August - 2014 01-Aug-2014
September - 2014 01-Sep-2014
October - 2014 01-Oct-2014

date always 1st of the month

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-11-26 : 01:48:30

i got solution.......... as below

declare @p_startday datetime= '1 jun 2014 ', @p_endday datetime= '11 may 2015'

;with
date as
( select 1 as level,
dateadd(mm,0,@p_startday) as month

union all

select date.level + 1 as level,
dateadd(mm, date.level ,@p_startday) as month
from date
where level <= datediff(mm,@p_startday,@p_endday)
)

select datename(month, date.month) + ' - ' + datename(year, date.month)as mtext,
convert (varchar(max),dateadd(month, datediff(month, 0, date.month),0) , 106) as mvalue
from date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources