American date or: Marital crisis

First of all, it's important to note the following: I live in Australia, and in Australia, like in most of the world, the date format is day - month - year.
My husband had asked me this week how should he change the format of the date and then get the day out of the data.
Simple, I said, use the TEXT function to change the format, and use the day () function on the date.
"I get #value! error" he winged, and I thought I needed to help him, only to find out that he was right (see below use of functions and results):
The formulas:

Excel date formulas

Excel date results

I thought maybe to use Google Spreadsheet, but unfortunately and surprisingly enough, they are not better:

Formulas are below:

Google docs formulas

Google Docs values

Partition by Half-a-year using windows functions

Let's say you're using Windows functions, and you want to partition by a quarter.
That's simple, you'd just do:

SELECT SUM ( [MyValue]) OVER (PARTITION BY [Employee], YEAR ([Valuation date]), DATEPART (qq,[Valuation date])  ORDER BY ROW_ID)

But what happens if you would like to run a sum by half a year? If it's just getting the value (first or second half of year), you'd play around with

SELECT CASE WHEN month < 7 THEN 1 ELSE 2 END AS [HalfAYear]

 or with

SELECT CASE WHEN DATEPART (qq, Valuation date)  < 3 THEN 1 ELSE 2 END AS [HalfAYear]

 and that would do.
But since we need a running sum per half a year, you'd like to use this code:

SUM ( [MyValue]) OVER (PARTITION BY [Employee], YEAR ([Valuation date]), YEAR (DATEADD(m, 6, [Valuation date]))  ORDER BY ROW_ID)

Using the YEAR for the first time gives me the current year, when I'm using YEAR over "add 6 month" I'm using the "Australian Financial Year", but this way I am partitioning by half a year. Please note that at the end of the day I don't know which half it is, but it doesn't matter, since I don't care about the label, I care about the results.

Calculating the Week Number for the Australian Financial Year

In Australia, the Financial Year Starts at the 1st of July.
In my cube, they wanted to browse by the week by of the Financial Year.
I've created two new columns: one for the Week of the Year, the other for the Week of the Financial year.

While the first column is easily populated

UPDATE dimDate
SET WeekYear = datepart (ww, myDateFormat)


The second one is a bit more complicated:
The year start at 1/7/2013, so I want that week to start as 1. So I need the WeekYear - week number of 1/7/2013.
I added 1 because we start at 1, not 0.


UPDATE [dimDate]
WeekFinancialYear = WeekYear - DATEPART (ww, CAST (CAST([CalanderYear] AS CHAR(4)) + '0701' AS DATE)) + 1
WHERE [month] >=7


But that's for months in the beginning of the financial year, months that their number is bigger than 7.

Otherwise, Just add 26 (this is the number of 1/2 a year).


UPDATE dimDate
SET WeekFinancialYear = WeekYear + 26
WHERE [month] < 7






(BTW if you're looking for a job here down under, between the last week of June and the First week of August then you're quite hopeless. Or Week 52 till week 5 of the Financial Year!).