question

taj avatar image
taj asked

query migration from oracle to sql server 2008

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 ); how to migrate this query in MS SQL Server
sql-server-2008oracle
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SQLShark avatar image @SQLShark commented ·
It might be easier to rewrite the query rather then try to convert it. What are you trying to achieve?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
Oleg avatar image
Oleg answered
It is true that it would be much easier to rewrite the query from scratch because of the differences in syntax (there is no **connect by** in T-SQL). Here is the query which still accepts 2 parameters. The parameters are named almost the same except they need the @ sign in front of the parameter name: -- these are parameters to the query (instantiated with values): declare @p_startday datetime = '20140115'; declare @p_endday datetime = '20141015'; -- this is the query which uses the parameters: ;with dates as ( select dateadd(month, datediff(month, 0, @p_startday) + number, 0) TheDate from master.dbo.spt_values where type = 'P' and number between 0 and datediff(month, @p_startday, @p_endday) + 1 ) select datename(month, TheDate) + ' - ' + datename(year, TheDate) mText, convert(varchar(11), TheDate, 106) mValue from dates; If you run the query above, you will get this: mText mValue ------------------------- ----------- January - 2014 01 Jan 2014 February - 2014 01 Feb 2014 March - 2014 01 Mar 2014 April - 2014 01 Apr 2014 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 November - 2014 01 Nov 2014 You can create a stored procedure to run this query, it will accept 2 parameters and return the rows you need: set ansi_nulls on; set quoted_identifier on; go create proc dbo.usp_getMonth ( @p_startday datetime, @p_endday datetime ) as begin; set nocount on; ;with dates as ( select dateadd(month, datediff(month, 0, @p_startday) + number, 0) TheDate from master.dbo.spt_values where type = 'P' and number between 0 and datediff(month, @p_startday, @p_endday) + 1 ) select datename(month, TheDate) + ' - ' + datename(year, TheDate) mText, convert(varchar(11), TheDate, 106) mValue from dates; set nocount off; end; go Once the procedure is created, you can execute it like this: declare @p_startday datetime = '20140115'; declare @p_endday datetime = '20141015'; exec dbo.usp_getMonth @p_startday, @p_endday; go Hope this helps. Oleg
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

taj avatar image taj commented ·
thanks for !dea
0 Likes 0 ·
taj avatar image
taj answered
> -- i got the solution regarding above problem declare @p_startday datetime= '1 jun 2014 ', @p_endday datetime= '11 may 2015' ;WITH ctemonth AS ( SELECT 1 AS level , DATEADD(mm, 0, @p_startday) AS month UNION ALL SELECT ctemonth.level + 1 AS level , DATEADD(mm, ctemonth.level, @p_startday) AS month FROM ctemonth WHERE level <= DATEDIFF(mm, @p_startday, @p_endday) ) SELECT DATENAME(month, ctemonth.month) + ' - ' + DATENAME(year, ctemonth.month) AS mtext , CONVERT(VARCHAR(MAX), DATEADD(month, DATEDIFF(month, 0, ctemonth.month), 0), 106) AS mvalue FROM ctemonth`enter code here` ORDER BY month
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@taj This code is not short and is actually more heavy when compared with the script in my answer. True that **connect by level** in Oracle can be translated into recursive CTE in T-SQL, but there is no reason to use it for something that simple.
0 Likes 0 ·
taj avatar image taj commented ·
yes u r right... but can we replace 'from master.dbo.spt_values where type = 'P'' this from your code...
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@taj Why do you need to replace it? You have this table already in the master database and every user who has access to your SQL Server already has implicit permissions to select from it. If you really do need to replace it with some other table, you could opt to create a small table named Tally (almost everyone dealing with database development in SQL Server end up with having such table anyway). The Tally table usually has just one column with ever-increasing values starting from 1 ang going as far as you need for your purposes. For example, suppose 10,000 records is enough for you (you can add more later). Then you can create this table like this:

create table dbo.Tally(
Number int not null 
constraint PK_Tally primary key clustered
);
go

insert into dbo.Tally
select
    top 10000
    row_number() over (order by a.[object_id]) Number
    from sys.all_columns a cross join sys.all_columns b


Now you have the table ready and can use it in place of the table in my answer if you don't want to use that one. Oleg
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.