How can format this date field into yyyy-mm?
I am doing monthly report which counts items by date field.
ID DateReceived Items Status
2 2002-10-20 3 active
3 2006-02-22 4 inactive
From this I do monthly counting and get something like this:
Month TotalItem Totalactive TotalInactive
2002-02 20 3 4
2006-10 33 3 3
The reason I use this 'yyyy-mm' is to sort them in montly order.
I already have working sql for the excel/access but I migrate the data to the sql server now.
I used this format(DateReceived, 'yyyy-mm') but sql doesn't allow this function.
I tried this,
convert(varchar(4),month()) + '-' + convert(varchar(2),month())
but this give 2002-2....instead of one i need like 2002-02.
I also tried Left(Datereceived, 7) but this gave out October 20 instead of 2007-10.