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(
,interval i*7 day)
, "%D %M %Y") as From_Day
, date_format(
least(
,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
,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
as From_Day
, case when
> '2005-06-23'
then '2005-06-23'
else
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
< '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

## Content

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

#### Have a question for an expert?

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.

## SearchDataManagement

• ### Potent NoSQL architecture engaged for building new applications

Behind hyperbolic terms like 'digital transformation' are innovative systems of engagement. DataStax CEO Billy Bosworth joins ...

• ### Finance data governance program gets new start, broader focus

Businesses constantly need to evolve their programs for governing data. Nationwide's finance data governance team shares how it ...

• ### Pepperdata calls on Dr. Elephant for big data application profiling

Application profiling software from Pepperdata is built on LinkedIn's Dr. Elephant open source entry. A primary goal is to get ...

• ### Deloitte report reveals the power of unstructured data analytics

The analysis of unstructured data and other so-called dark data types can deliver significant business value, according to a new ...

• ### Ease of use is top priority in selecting self-service analytics tools

A lot of factors go into a strong, modern BI tool, but users say that ease of use trumps all other considerations when picking ...

• ### Three predictive modeling flaws that cripple data science projects

Data science can be incredibly valuable if done right, but just as damaging if done wrong. Here, a data science expert discusses ...

## SearchSAP

• ### SAP Vora updates target business insights from Hadoop big data

SAP Vora has been updated to include features that make it easier to deploy and use to get insights from Hadoop big data; SAP IBP...

• ### Chatbots provide faster self-service on SAP systems

The SAP SuccessFactors partnership with collaboration software vendor Slack is just one of many efforts to make little robotic ...

• ### SAP tops list of ERP vendors, but market is more evenly divided

Panorama Consulting says SAP continues as market leader among the top four ERP vendors. But the market has evened, and Microsoft ...

## SearchSQLServer

• ### Redgate tools help bring database DevOps to Visual Studio 2017

So far, data has been left out of the discussion. But database DevOps can grow if tools like Redgate's package for Visual Studio ...

• ### Conquer real-time operational analytics in SQL Server 2016

Analytics helps improve business operations, and SQL Server 2016 users can master it -- in real time, using operational data in a...

• ### SQL Server machine learning goes full throttle on operational data

Artificial intelligence is a hot topic in IT, and Microsoft has made strides to synchronize SQL Server with machine learning ...

## TheServerSide

• ### Amazon S3 outage a Fukushima moment for cloud computing

The Amazon S3 outage has turned into the Fukushima moment of cloud computing, as users re-evaluate the cloud's long-term ...

• ### GPU computing key to machine learning and big data performance

While the CPU remains central to data processing, massive gains in the area of AI analytics and dig data performance are being ...

• ### Why scaling Agile development calls for DevOps toolchain integration

Learn how Tasktop is battling the cost of scaling Agile development and the DevOps toolchain by integrating top tools.

## SearchDataCenter

• ### IBM's cloud dreams soar on the wings of AI, open source

Hoping to play catch-up with its web services archrivals, IBM has rolled out a raft of products and services fueled by AI and ...

• ### Fortify IoT infrastructure now, even for simple deployments

The IoT deployments in the enterprise today may not tax the current infrastructure, but it's not too soon to prep for the demands...

• ### Data center storage architecture moves toward software-defined memory

New IT developments converge storage and memory into a hybrid approach. Consequently, the idea of software-defined memory starts ...

## SearchContentManagement

• ### Microsoft Teams joins growing business chat software market

The general release of Microsoft's latest product puts Slack square in its scope, with hopes of taking a chunk from the business ...

• ### Headless CMS supplements traditional web content management

Headless CMS can be a difficult pivot for dyed-in-the-wool legacy shops, but remixing content in this new model with RESTful APIs...

• ### Workplace collaboration tools aren't a nice-to-have anymore

Workplace collaboration software isn't just about convenient employee chat options. It's making employees and external partners ...

## SearchFinancialApplications

• ### Report rates e-sourcing, spend analysis and contract management tools

Gartner Magic Quadrant finds plenty of room for growth in market for cloud-based strategic sourcing application suites that can ...

• ### Benefits administration systems that use analytics liked by employers

HR tech systems are increasingly using analytics in benefits administration, including using claims data; meanwhile, APIs are ...

• ### Degreed integrates and organizes content from online learning software

Atlassian and Intel use cloud-based Degreed to integrate e-books, articles, videos and other content and recommend training for ...

Close