I am reviving this old thread to announce that I wrote a patch that implements the %V, %u and %G directives from (unix) `strftime()`. Briefly, %V is the week number as ISO 8601 defines it, %G is the corresponding year (since a week is considered to belong to the year that holds most of its days), and %u is the weekday with 1=Monday, 7=Sunday.
Without further ado, here is the patch:
```
Index: src/date.c
==================================================================
--- src/date.c
+++ src/date.c
@@ -989,17 +989,20 @@
**
** Return a string described by FORMAT. Conversions as follows:
**
** %d day of month
** %f ** fractional seconds SS.SSS
+** %G year 0000-9999 of current week's Thursday
** %H hour 00-24
** %j day of year 000-366
** %J ** julian day number
** %m month 01-12
** %M minute 00-59
** %s seconds since 1970-01-01
** %S seconds 00-59
+** %u day of week 1-7 monday==1
+** %V week of year 01-53 week belongs to year with most of its days
** %w day of week 0-6 sunday==0
** %W week of year 00-53
** %Y year 0000-9999
** %% %
*/
@@ -1025,13 +1028,15 @@
case 'd':
case 'H':
case 'm':
case 'M':
case 'S':
+ case 'V':
case 'W':
n++;
/* fall thru */
+ case 'u':
case 'w':
case '%':
break;
case 'f':
n += 8;
@@ -1038,10 +1043,11 @@
break;
case 'j':
n += 3;
break;
case 'Y':
+ case 'G':
n += 8;
break;
case 's':
case 'J':
n += 50;
@@ -1117,13 +1123,42 @@
sqlite3Int64ToText(iS, &z[j]);
j += sqlite3Strlen30(&z[j]);
break;
}
case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
+ case 'u': {
+ z[j++] = (char)(((x.iJD+43200000)/86400000) % 7) + '1';
+ break;
+ }
case 'w': {
z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
break;
+ }
+ case 'V': /* Fall thru */
+ case 'G': {
+ DateTime y = x;
+ computeJD(&y);
+ y.validYMD = 0;
+ /* Adjust date to Thursday this week:
+ The number in parentheses is 0 for Monday, 3 for Thursday */
+ y.iJD += (3 - (((y.iJD+43200000)/86400000) % 7))*86400000;
+ computeYMD(&y);
+ if( zFmt[i]=='G' ){
+ sqlite3_snprintf(5,&z[j],"%04d",y.Y); j+=sqlite3Strlen30(&z[j]);
+ }else{
+ /* borrowed code from case 'j' */
+ sqlite3_int64 tJD = y.iJD;
+ int nDay;
+ y.validJD = 0;
+ y.M = 1;
+ y.D = 1;
+ computeJD(&y);
+ nDay = (int)((tJD-y.iJD+43200000)/86400000);
+ sqlite3_snprintf(3, &z[j],"%02d",nDay/7+1);
+ j += 2;
+ }
+ break;
}
case 'Y': {
sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
break;
}
```
This is unencumbered code. I wrote it from scratch, only drawing inspiration (and copying/modifying snippets) from the sqlite source. Anyone, including the sqlite core team, is allowed to use it as they see fit. I am willing to sign papers to that effect. But of course, even though I have tested the code and believe it to be correct, I do not accept liability if there is a bug. Use at your own risk.
I would be happy if code resembling this were to make it into the official sqlite source. ISO week numbers are used all over the place in Europe, after all.