Re: [sqlite] Easiest way to get day of week as a string (not a number)?
> On Feb 1, 2018, at 10:57 AM, Chris Green wrote: > > But systems have language variables which tell which set to use. Your code that runs the query can use those system APIs to localize the day names. Just have the query return the raw date strings (or timestamps or whatever) and do the fancy manipulation in code. Or you could write an extension function that looks up the localized day name, and call that function in your query. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easiest way to get day of week as a string (not a number)?
R Smith wrote: > 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. > But systems have language variables which tell which set to use. The C strftime function has just about every form of day and month name you can imagine and presumably that works with the locale setting to give them to you in your own language. -- Chris Green · ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easiest way to get day of week as a string (not a number)?
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! Ryan 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 substr('SunMonTueWedThuFriSat',strftime('%w',Date)*3+1,3); without defining your own functions. From: Chris Green<mailto:c...@isbd.net> Sent: 01 February 2018 13:13 To: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easiest way to get day of week as a string (not a number)?
x wrote: > Don’t think you’ll get it any less ugly than > > substr('SunMonTueWedThuFriSat',strftime('%w',Date)*3+1,3); > Yes, thanks, that's where I had got to! :-) -- Chris Green · ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easiest way to get day of week as a string (not a number)?
Don’t think you’ll get it any less ugly than substr('SunMonTueWedThuFriSat',strftime('%w',Date)*3+1,3); without defining your own functions. From: Chris Green<mailto:c...@isbd.net> Sent: 01 February 2018 13:13 To: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> 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? -- Chris Green · ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easiest way to get day of week as a string (not a number)?
On Thu, Feb 1, 2018 at 6:55 AM, Chris Green wrote: > 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? > -- horrible way SELECT CASE strftime("%w",DateInRow) WHEN 0 THEN "Sun" WHEN 1 THEN "Mon" ... WHEN 6 THEN "Sat" END AS DayOfWeek > > -- > Chris Green > · > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- I have a theory that it's impossible to prove anything, but I can't prove it. Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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? -- Chris Green · ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users