Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Date Range for SQL and Oracle.

  • 12 April 2013
  • Author: Anil
  • Number of views: 11029
  • 0 Comments

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 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

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating
Anil

AnilAnil

Highly motivated Business Intelligence Engineer having leadership abilities and team work skills as well as the ability to accomplish tasks under minimal direction and supervision. Has more than 5 years of development & implementation experience in HealthCare Data Analytics and Telecommunication

Other posts by Anil

Full biography Contact author

Please login or register to post comments.