[SQLPlus] Extracting dates per week

Spider · 7 Dec 2006 at 15:10

We have a database which stores dates of transactions and other associated info.
I want to product a weekly summary for the last year, along the lines of:

Code:

Week			# Transactions
-------			---
Week1			40
Week2			86
...
Week 51			55
Week52			22

But don’t want to manually enter the date ranges because I’m too lazy!

Is there any more efficient way of getting the data out of the database?

Thanks for any tips!

Mr^B · 7 Dec 2006 at 15:34

If it was MSSQL, you can use the following;

Code:

SELECT 'Week ' +  LTRIM(STR(DATEPART(ww , [date]))) AS Week FROM[table]

Substituting [date] for your date field, etc.

rpstewart · 7 Dec 2006 at 16:07

DB2 SQL has a week() function which will give you a week number from a date or timestamp. Chances are there will be something similar in SQLPlus

happytechie · 7 Dec 2006 at 16:51

Oracle 10g SQL language reference manwell here

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

the IW element of the DateTime structure is what you want iirc