Hello all,

This will be my memorable blog post for this year. Since, this is my last blog post for this year acc to Nepali year.We are celebrating happy new year(01/01/2070 B.S) day after tomorrow :). so, want to wish you all happy Nepali new year.

This blog is related to the Date Range. While working I need to generate the date range for Oracle side date range from start date to enddate(i.e. 2010-01-01 to 2012-01-01) . Though it’s easy for me in SQL side but in oracle side I have to do some research for this since I am not an Oracle guy.

I think there might be different alternative way for this but If you want to generate the date range from startdate to enddate, then you can simply run the script as below.

For SQL Date Range script:

--startdate: 2010-01-01 enddate :2012-01-01
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DateRange') 
DROP TABLE DateRange
GO
CREATE TABLE [dbo].[DateRange](
	DateRange DATE
)
GO
DECLARE @StartDate Date;
DECLARE @EndDate Date;
SET @StartDate='2010-01-01'
SET @EndDate='2012-01-01'
WHILE (@StartDate<@EndDate)
BEGIN
 INSERT INTO  DateRange
 VALUES(@StartDate)
 SELECT @StartDate = DATEADD(month,1,@StartDate)--for month wise increment
 --SELECT @StartDate = DATEADD(year,1,@StartDate)--for year wise 
-- SELECT @StartDate = DATEADD(day,1,@StartDate) --for day wise
END
GO
--SELECT * FROM DateRange

For Oracle Date Range Script:

DECLARE
   does_not_exist   EXCEPTION;
   PRAGMA EXCEPTION_INIT (does_not_exist, -942);
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE DateRange';
EXCEPTION
   WHEN does_not_exist
   THEN
      NULL;
END;
/      
  CREATE TABLE DateRange (
  DateRange DATE 
)   
/                      
DECLARE
    vblStartdate DATE;
    vblEnddate DATE;
BEGIN 
    vblStartdate := To_Date('2010-01-01','YYYY-MM-DD');
    vblEnddate := To_Date('2012-01-01','YYYY-MM-DD');

    WHILE (vblStartDate <vblEndDate) 
    LOOP
EXECUTE IMMEDIATE 'INSERT INTO DateRange SELECT '''||vblStartDate||''' FROM DUAL';
EXECUTE IMMEDIATE ('SELECT Add_Months('''||vblStartDate||''',1) FROM DUAL')INTO  vblStartDate;
    END LOOP;
END;
/   
--select * from DateRange

OUTPUT:

DateRange -- month wise increment from startdate to enddate
2010-01-01
2010-02-01
2010-03-01
2010-04-01
2010-05-01
..........
2011-12-01

I think this post will be helpful for some of us who is looking for such date range.

Once again Happy Nepali New YEAR 🙂 !!!

Thanks,

Anil Maharjan