How to get the Missing dates between two dates in the table? | Unix Linux Forums | UNIX for Advanced & Expert Users

  Go Back    


UNIX for Advanced & Expert Users Expert-to-Expert. Learn advanced UNIX, UNIX commands, Linux, Operating Systems, System Administration, Programming, Shell, Shell Scripts, Solaris, Linux, HP-UX, AIX, OS X, BSD.

How to get the Missing dates between two dates in the table?

UNIX for Advanced & Expert Users


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 02-25-2013
Venkatesh1 Venkatesh1 is offline
Registered User
 
Join Date: Jul 2012
Last Activity: 29 October 2013, 2:19 AM EDT
Location: chennai
Posts: 62
Thanks: 6
Thanked 0 Times in 0 Posts
How to get the Missing dates between two dates in the table?

Hi Am Using Unix Ksh ...
I have a Table called date

Code:
select * from date ;

Date 
01/02/2013
06/02/2013

I need the output as

Code:
Missing Date 
01/02/2013 
02/02/2013
03/02/2013
04/02/2013
05/02/2013
06/02/2013

Can anyone help me pls............
Sponsored Links
    #2  
Old 02-25-2013
shamrock shamrock is offline Forum Advisor  
Registered User
 
Join Date: Oct 2007
Last Activity: 12 April 2014, 1:48 PM EDT
Location: USA
Posts: 1,436
Thanks: 19
Thanked 134 Times in 126 Posts
I am no dba but what i would do is dump the table into a flat file...fill in the missing date time rows using perl...load it back into the date table...and if all of this sounds like baloney feel free to ignore it...
Sponsored Links
    #3  
Old 02-25-2013
Yoda's Avatar
Yoda Yoda is offline Forum Advisor  
Jedi Master
 
Join Date: Jan 2012
Last Activity: 22 April 2014, 6:27 PM EDT
Location: Galactic Empire
Posts: 3,283
Thanks: 227
Thanked 1,158 Times in 1,095 Posts
You can use ALL_OBJECTS to generate missing dates:

Code:
select to_char(to_date('01/02/2013','dd/mm/yyyy') - 1 + rownum, 'dd/mm/yyyy') from ALL_OBJECTS
where  to_date('01/02/2013','dd/mm/yyyy') - 1 + rownum <= to_date('06/02/2013','dd/mm/yyyy');
01/02/2013
02/02/2013
03/02/2013
04/02/2013
05/02/2013
06/02/2013

Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Generating dates between two dates sathishsr Shell Programming and Scripting 4 08-09-2012 12:41 AM
How to write the dates between 2 dates into a file dsfreddie UNIX for Dummies Questions & Answers 5 06-20-2012 09:11 PM
Replacing dates]] with (dates)]] lawstudent Emergency UNIX and Linux Support 20 01-19-2011 03:13 PM
SQL: find if a set od dates falls in another set of dates vertical98 Programming 0 10-16-2010 01:31 AM
Need script to generate all the dates in DDMMYY format between 2 dates frozensmilz Shell Programming and Scripting 2 01-29-2009 05:06 AM



All times are GMT -4. The time now is 08:08 AM.