CODEWITHSUNDEEP
sqlsql-server-2008
Geeme
Geeme

Reputation: 415

Add one month until it is greater than some value

I have start date & Mdate as columns in table, i want to do something like below in SQL

Add 1 month to Start_Date until start date > Mdate

I tried using while and if concepts, but no luck. 

DECLARE @MIGRATIONDATE DATE, @STRT_DATE DATE, @NEXTD DATE
SET @MIGRATIONDATE =20140725
SET @STRT_DATE = 20140521

SELECT WHILE ( @STRT_DATE > @MIGRATIONDATE)
         BEGIN 
             DATEADD(MM,1,@STRT_DATE))
         END

appreciate if you can guide me on this?

Upvotes: 1

Views: 1090

Answers (3)

user3864233
user3864233

Reputation: 514

select 
     case when start_date<Mdate then dateadd(mm,1,start_date) else start_date end
   from yourTable

suppose start_date is 20140721 and MigrationDate is 20140525 then it returns you with gives you accepted result

select case 
        when convert( date,'20140721') <convert(date,'20140525')
         then dateadd(mm,1,'20140721') else '20140721' end

Upvotes: 1

Linger
Linger

Reputation: 15048

How about (SQL Fiddle):

SELECT DATEADD(month, 
               DATEDIFF(month, Start_Date, Mdate) + 1, 
               Start_Date) AS NEW_START_DATE
FROM MyTable;

If there is a possibility of the Start_Date being greater than or equal to Mdate then use the following (SQL Fiddle):

SELECT Start_Date AS OLD_START_DATE,
      CASE WHEN DATEDIFF(month, Start_Date, Mdate) > 0 
      THEN DATEADD(month, DATEDIFF(month, Start_Date, Mdate) + 1, Start_Date)
      ELSE Start_Date END  AS NEW_START_DATE,
      Mdate
FROM MyTable;

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

I do not know if you would like to get one row of that table. If you want to do it for each row, then you should wrap it in a function and use CROSS APPLY.

declare
@startdate datetime, 
@enddate datetime

set @startdate = '20140101'
set @enddate = '20150101'

;WITH date_range (thedate) AS (  
  select @startdate  
  UNION ALL SELECT DATEADD(MONTH, 1, thedate)  
            FROM date_range  
            WHERE DATEADD(MONTH, 1, thedate) <= @enddate  
            )  
SELECT  thedate FROM date_range

If you wanted in a function:

CREATE FUNCTION [dbo].[ExplodeDates](@startdate datetime, @enddate datetime)
returns table as
return (

WITH date_range (thedate) AS (  
  select @startdate  
  UNION ALL SELECT DATEADD(DAY, 1, thedate)  
            FROM date_range  
            WHERE DATEADD(DAY, 1, thedate) <= @enddate  
            )  
SELECT  thedate FROM date_range   
);


SELECT Id,thedate
FROM   Table1 T1
       CROSS APPLY [dbo].[ExplodeDates](T1.StartDate,T1.EndDate)

Upvotes: 1

Related Questions