CODEWITHSUNDEEP
sqldate
Boumbles
Boumbles

Reputation: 2523

Getting the range in SQL when given between

I am wondering if it's possible (without actually parsing the given string) to get the actual range (in terms of days, minutes or seconds) that is specified when you have an SQL statement like 

[select 'x'
from dual
where date between to_date('20111113152049')
and to_date('20120113152049')]

I am working on a query where I'm given a string in the form of 

"between to_date(A) and to_date(B)"

and would like to get that value in days to compare to a policy we let the user set so they don't enter a date range longer than say a week.

Upvotes: 2

Views: 142

Answers (1)

rene
rene

Reputation: 42414

Assuming you're looking for a theoretical answer (that is: don't take this into production) this could work:

Prerequistes: have three tables: days_seq(day_seq), month_seq(mth_seq) and year_seq(yr_seq) days has the numbers 1...31, month 1..12, years 2011....?

Use te following query (I used access because I don't have proper RDBMS available here, keep in mind that MS-ACCESS/JET is forgiving in the use of the Dateserial function, that is, it doesn't break when you ask the dateserial for february, 30th, 2012)

SELECT Max(DateSerial(
    [year_seq]![yr_seq]
   ,[month_seq]![mth_seq]
   , [days_seq]![day_seq])) 
   -
   Min(DateSerial(
     [year_seq]![yr_seq]
    ,[month_seq]![mth_seq]
    ,[days_seq]![day_seq])) AS days
FROM days_seq, month_seq, year_seq
WHERE DateSerial(
        [year_seq]![yr_seq]
       ,[month_seq]![mth_seq]
       ,[days_seq]![day_seq])
BETWEEN #2012-02-1# AND #2012-02-28#

The query basically produces a carthesian product of three tables which generates all possible days in months, months in a year for as many years as you have in the years table.

Bonus: You could off-course generate a permanent Calendar table as X-Zero suggests.

table calendar([date])

  INSERT INTO calendar
    SELECT DISTINCT DateSerial(
        [year_seq]![yr_seq]
       ,[month_seq]![mth_seq]
       , [days_seq]![day_seq])) 
    FROM days_seq, month_seq, year_seq

You still have to pick your start year and your end year wisely. According to the Maya's an enddate of december 21st, 2012 will do.

Upvotes: 1

Related Questions