sandeep169 Posted April 14, 2016 Report Share Posted April 14, 2016 E below Oracle query ni SQL server ki compatible ga ela marchali. I'm confused with Level and Connect by Level, endukante sql server doesn't have this concept. Kani accommodate cheyali ante ela cheyali..help chesi punyam katukondi brothers (SELECT DISTINCT last_day(mydate) AS eom_date FROM ( select TRUNC (ADD_MONTHS (SYSDATE, -12), 'yy') - 1 + LEVEL AS mydate FROM dual CONNECT BY LEVEL <= (SELECT TRUNC (ADD_MONTHS (SYSDATE, ), 'yy')- TRUNC (ADD_MONTHS (SYSDATE, -12), 'yy') FROM DUAL))) Quote Link to comment Share on other sites More sharing options...
Katravelli Posted April 14, 2016 Report Share Posted April 14, 2016 SELECT DISTINCT last_day(mydate) AS eom_date FROM ( select TRUNC (DATEADD (MONTH, -12, GETDATE()), 'yy') - 1 + LEVEL AS mydate BY LEVEL <= (SELECT CONVERT(FLOAT, TRUNC (DATEADD (MONTH, ), GETDATE(), 'yy'))- CONVERT(FLOAT, TRUNC (DATEADD (MONTH, -12, GETDATE()), 'yy')) FROM DUAL))) Quote Link to comment Share on other sites More sharing options...
mainst Posted April 14, 2016 Report Share Posted April 14, 2016 (SELECT DISTINCT last_day(mydate) AS eom_date FROM ( select TRUNC (DATEADD (MONTH, -12, GETDATE()), 'yy') - 1 + LEVEL AS mydate BY LEVEL <= (SELECT CONVERT(FLOAT, TRUNC (DATEADD (MONTH, ), GETDATE(), 'yy'))- CONVERT(FLOAT, TRUNC (DATEADD (MONTH, -12, GETDATE()), 'yy')) FROM DUAL))) Quote Link to comment Share on other sites More sharing options...
sandeep169 Posted April 14, 2016 Author Report Share Posted April 14, 2016 Thank you bhayya ...kani SQL Server lo Level undadu and TRUNC kuda predefined functions kaavu..akkade I'm struck to check any alternative for this ani Quote Link to comment Share on other sites More sharing options...
riashli Posted April 14, 2016 Report Share Posted April 14, 2016 Quote Link to comment Share on other sites More sharing options...
loveindia Posted April 14, 2016 Report Share Posted April 14, 2016 Man, as an FYI, The equivalent of Level in PL/SQL is Recursion. You can do that using a CTE. Let me see this better and write back in an another post man.. Quote Link to comment Share on other sites More sharing options...
loveindia Posted April 14, 2016 Report Share Posted April 14, 2016 SELECT DATEADD(yy, DATEDIFF(yy,0, DATEADD(MONTH, -12, GETDATE())), 0) - 1 this is something you can use for the inner part of the query that uses TRUNC and ADD_MONTHS. It is actually subracting 12 months and getting the first day of that resulted year and subracting 1 day at the end and a level is added at the end. It is like an auto incrementing number 1,2,3,.... How you will re-write that totally depends on what you want and the other tables in sql server man... Hope that helps... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.