The reason day-names are not part of the standard set of date-time functions is that they are not standard and certainly not international.

For you it is "Mon, Tue, Wed, Thu, Fri, Sat Sun"...

But for me it might be:

"Lun, Mar, Mer, Jeu, Ven, Sam, Dim" or sometimes simply "Lu, Ma, Me, Je, Ve, Sa, Di" etc.

You could also JOIN against a sub-query enumerating the names, or even against a CTE defining it, or include in your DB another table or perhaps a View in which you define, well, any list to join against.

My favourite trivial UDF is an ELT(i, x1[, x2, x3...xn]) function (à la MySQL) that takes an Index as the first parameter and elects the i-th item from the subsequent list of parameters (x1..xn) to return, but if that's all you wish to achieve, that substr() solution is by far the simplest and quickest for day names.

Once the list gets long however, like enumerating Month names, or year names (like Chinese year names) then a table/view/cte join starts looking much simpler and certainly more elegant. Also, if your system needs to be multi-lingual in any way, using proper tables is really the only good option.

"Many ways to skin a cat" vs. "Right tool for the job" an' all that....

Best of luck!

PS: Isn't it horrible that at some point in our recent past, skinning a cat was not an atrocious thing....

On 2018/02/01 3:50 PM, x wrote:
Don’t think you’ll get it any less ugly than


without defining your own functions.

From: Chris Green<>
Sent: 01 February 2018 13:13
Subject: [sqlite] Easiest way to get day of week as a string (not a number)?

I want to get Sun, Mon, Tue, Wed etc. from a date, what's the easiest
way of doing this in a sqlite select?

I guess I can do something (horrible?) with the numeric day of week
and substr() but is there not an easier way?

sqlite-users mailing list

Reply via email to