Easiest way to get day of week as a string (not a number)?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
cl
Reply | Threaded
Open this post in threaded view
|

Easiest way to get day of week as a string (not a number)?

cl
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

curmudgeon
In reply to this post by cl
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:[hidden email]>
Sent: 01 February 2018 13:13
To: [hidden email]<mailto:[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
Waiting for someone to post a CTE solution along the lines of

WITH weekday(dayno,dayname) AS (SELECT (0,'Sun) ....) .. SELECT dayname FROM weekday WHERE dayno = strftime(...).

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Donnerstag, 01. Februar 2018 14:50
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] 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:[hidden email]>
Sent: 01 February 2018 13:13
To: [hidden email]<mailto:[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
cl
Reply | Threaded
Open this post in threaded view
|

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

cl
In reply to this post by curmudgeon
x <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

R Smith-2
In reply to this post by curmudgeon
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:[hidden email]>
> Sent: 01 February 2018 13:13
> To: [hidden email]<mailto:[hidden email]>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
cl
Reply | Threaded
Open this post in threaded view
|

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

cl
R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Jens Alfke-2


> On Feb 1, 2018, at 10:57 AM, Chris Green <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users