Q
Problem solve Get help with specific problems with your technologies, process and projects.

Generating week dates

I have two dates: 10th May 2005 and 23rd June 2005. I have to display the weeks for those dates. Can you please help me out in writing a SQL query for this kind of result?

I have two dates: 10th May 2005 and 23rd June 2005. I have to display the weeks for those dates. A week means the first date + 7, next week will be the (first date + 7) + 7, etc. It will continue till the last date entered. For our example it will be:

Week 1: 10th May 2005 - 16th May 2005
Week 2: 17th May 2005 - 23rd May 2005
Week 3: 24rd May 2005 - 30th May 2005
Week 4: 31st May 2005 - 6th June 2005
Week 5: 7th June 2005 - 13th June 2005
Week 6: 14th June 2005 - 20th June 2005
Week 7: 21st June 2005 - 23rd June 2005

Can you please help me out in writing a SQL query for this kind of result?

How much of this problem you choose to do in SQL is really your decision. It can be argued that date formatting should not be done in SQL, but rather in the application layer.

Compare this MySQL solution:

select i+1 as WeekNo
     , date_format(
       date_add('2005-05-10'
                 ,interval i*7 day)
                  , "%D %M %Y") as From_Day  
     , date_format(
       least( 
       date_add('2005-05-10'
                 ,interval i*7+6 day)
            , '2005-06-23' )  
                  , "%D %M %Y") as To_Day        
  from ( 
       select 0 as i
       union all select 1
       union all select 2
       union all select 3
       union all select 4
       union all select 5
       union all select 6
       union all select 7
       union all select 8
       union all select 9 
       ) as integers   
 where date_add('2005-05-10'
               ,interval i*7 day)
              < '2005-06-23' 

with this Microsoft SQL Server solution:

select WeekNo
     , datename(day,From_Day)
      +case when day(From_Day)
              in (1,21,31) then 'st '
            when day(From_Day)
              in (2,22) then 'nd '
            when day(From_Day)
              in (3,23) then 'rd '
            else 'th ' end 
      +datename(month,From_Day)
      +' '   
      +datename(year,From_Day)  as From_Day  
     , datename(day,To_Day)
      +case when day(To_Day)
              in (1,21,31) then 'st '
            when day(To_Day)
              in (2,22) then 'nd '
            when day(To_Day)
              in (3,23) then 'rd '
            else 'th ' end 
      +datename(month,To_Day)
      +' '   
      +datename(year,To_Day)  as To_Day  
  from ( 
select i+1 as WeekNo
     , dateadd(day,i*7,'2005-05-10')
                      as From_Day  
     , case when
       dateadd(day,i*7+6,'2005-05-10') 
               > '2005-06-23'   
            then '2005-06-23'
            else
       dateadd(day,i*7+6,'2005-05-10') 
           end as To_Day        
  from ( 
       select 0 as i
       union all select 1
       union all select 2
       union all select 3
       union all select 4
       union all select 5
       union all select 6
       union all select 7
       union all select 8
       union all select 9 
       ) as integers   
 where dateadd(day,i*7,'2005-05-10') 
              < '2005-06-23'   
       ) as derived_table

Looking at that monstrosity, it's hard to find fault with the advice to do formatting in the application layer.

This was last published in July 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close