Dear all,

I was wondering if there was an easier way to do what I am trying, without having a table with all the dates and week numbers.

Scenario: Week number of a DateTime Field where the year does not start on January 01^st but April 1^st to 31 Mach.

Issue: A week always starts on a Monday so if the 1^st is on Tuesday, the first week is Tuesday Sunday, if the 1^st April is on Friday, the 1^st week is Friday Sunday and 7 day periods from there.

CREATE TABLE [dbo].[DailyCanx](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [DateCancelled] [datetime] NULL

) ON [PRIMARY]

ID            DateCancelled   WeekNo

1              Wed 1st April 2015           1

2              Mon 6th April 2015          2

3              Mon 13th April 2015        3

4              Sun 26th April 2015          4

I could create a table with all the start date and end dates of all the week numbers but I think there must be a better way.

I am based in the UK using SQL Server 2008 R2.

Cheers and thanks for all your time.

Alastair MacFarlane

Hello Alastair,

You can use the build-in function DATEPART to calculate the week/iso week

SELECT DATEPART(WEEK, GETDATE()), DATEPART(ISO_WEEK, GETDATE())

Olaf,

Thanks for the reply. The use of ISO_Week would help in the year in question is the calendar year but I am concerned with the year starting on April 01.

Can it be used for a year starting April 01 - March 31?

Thanks for your reply.

Alastair

Substract 3 month from the date value

SELECT DATEPART(WEEK, DATEADD(MONTH, -3, GETDATE())), 
       DATEPART(ISO_WEEK, DATEADD(MONTH, -3, GETDATE()))

Olaf,

Thanks. I should have engaged my brain rather than used the keyboard to do my 'thinking' for me.

Cheers.

Alastair

>> I was wondering if there was an easier way to do what I am trying, without having a table with all the dates and week numbers. <<

You will find that a Calendar table is a basic idiom in SQL programming. Remember this is a  data language. Your other choices are procedural programming and OO programming.  

>> Scenario: Week number of a DATETIME field [sic] where the year does not start on January 01^st [sic] but April 1^st to 31 March. [sic] <<

I often get the feeling that I am the only guy who ever read the ANSI/ISO Standards (I had to when I was on X3H2). In SQL, a field is part of a temporal value {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND}.  

Next, the one and only format used is ISO-8601 which is yyyy-mm-dd  and not the local dialect you used. The only ISO standard use more than this is the Metric system(SI Units).  Look at the great cartoon site  https://xkcd.com/1179/ to get feeling how silly dialect days are. Did you know that Czech and Slovak use totally different names for months? 

What you want seems to be fiscal calendar. So that would be 36,525 rows for a century of dates

>> Issue: A week always starts on a Monday so if the 1^st is on Tuesday, the first week is Tuesday Sunday, if the 1^st April is on Friday, the 1^st week is Friday Sunday and 7 day periods from there. <<

Have you read the ISO week definition?  The ISO-8601 standard also allows a week_date displayed as yyyyWww-d where ww= {'01' to '53'}  and d ={'1' to '7' 7=Sunday}. 

The IDENTITY table property cannot ever by a key or anything in a valid schema and certainly not a generic magical id!  Is this what you wanted? 

CREATE TABLE Daily_Canx
(cancellation_date DATE NOT NULL PRIMARY KEY);

Now create view with the different calendar scale.

CREATE VIEW Weekly_Canx
AS
SELECT D.cancellation_date AS cancellation_cal_date,
       C.cancellation_week_date,
  FROM Daily_Canx AS D, Calendar AS C
WHERE  D.cancellation_date = C.cal_date;

You get the week by substrings that chop off the '-d' part. This is popular in the Nordic countries (old joke: What time is it, Olaf?. January, Sven.)