Re: [sqlite] Easiest way to get day of week as a string (not a number)?

[Jens Alfke]

> 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)?

[Chris Green]

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)?

[R Smith]

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
Sent: 01 February 2018 13:13
To: 
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)?

[Chris Green]

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)?

[x]

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
Sent: 01 February 2018 13:13
To: 
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)?

[John McKown]

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