Hi All,
I still have the problem to set the result of the below statement to variable
so I can print out mulitple times without the executing the select statement
over and over again.
If you have a solution/syntax to set variable please share with me.
Thank,
JP
select '#device local time = ' ||
(case strftime('%w', d) when '0' then 'SUN' when '1' then 'MON' when '2' then
'TUE' when '3' then 'WED' when '4' then 'THUR' when '5' then 'FRI' when '6'
then 'SAT' end) || ' ' ||
(case strftime('%m', d) when '01' then 'JAN' when '02' then 'FEB' when '03'
then 'MAR' when '04' then 'APR' when '05' then 'MAY' when '06' then 'JUN' when
'07' then 'JUL' when '08' then 'AUG' when '09' then 'SEP' when '10' then 'OCT'
when '11' then 'NOV' when '12' then 'DEC' end) || ' ' ||
strftime('%d %H:%M:%S %Y', d,'localtime') || ', '
from (select CURRENT_TIMESTAMP as d) ;
----- Original Message ----
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Monday, July 28, 2008 10:57:22 AM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP
Hi,
Thanks for the big help.
Finally I got it worked as expected and the sql statement below to return the
format as: #device local time = MON JUL 28 10:57:30 2008.
Another question that I have is to set this select statement in the variable so
I can repeated to print out the variable again and again in different section
without repeat the long select statement
I would like to set the result of below statement to variable so I print it
again without repeating the long select statement. Would you please help.
Thanks,
JP
select '#device local time = ' ||
(case strftime('%w', d) when '0' then 'SUN' when '1' then 'MON' when '2' then
'TUE' when '3' then 'WED' when '4' then 'THUR' when '5' then 'FRI' when '6'
then 'SAT' end) || ' ' ||
(case strftime('%m', d) when '01' then 'JAN' when '02' then 'FEB' when '03'
then 'MAR' when '04' then 'APR' when '05' then 'MAY' when '06' then 'JUN' when
'07' then 'JUL' when '08' then 'AUG' when '09' then 'SEP' when '10' then 'OCT'
when '11' then 'NOV' when '12' then 'DEC' end) || ' ' ||
strftime('%d %H:%M:%S %Y', d,'localtime') || ', '
from (select CURRENT_TIMESTAMP as d) ;
----- Original Message ----
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, July 25, 2008 9:21:26 PM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP
"Joanne Pham" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> Hi all ,
> I have the following statement to convert the CURRENT_TIMESTAMP to
> format as
> TUE JULY 25 23:11:13 2008 but I got the empty string. Can you help me
> why the empty string is returned.
> Below is sql statement:
> select
> (case strftime('%w', d) when 0 then 'SUN' when 1 then 'MON’ when 2
> then ‘TUE’ when 3 then ‘WED’ when 4 then ‘THUR’ when 5 then ‘FRI’
> when 6 then ‘SAT’ end) || ‘ ‘ ||
> (case strftime('%m', d) when 1 then 'JAN' when 2 then ‘FEB’ when 3
> then ‘MAR’ when 4 then ‘APR’ when 5 then ‘MAY’ when 6 then ‘JUN’ when
> 7 then ‘JUL’ when 8 then “AUG’ when 9 then ‘SEP’ when 10 then ‘OCT’
> when 11 then ‘NOV’ when 12 then 'DEC' end)
>>> ' ' ||
> strftime('%d %H:%M:%S %Y', d)
> from (select CURRENT_TIMESTAMP as d);
Change strftime('%w', d) to CAST(strftime('%w', d) as INTEGER), and
similarly for strftime('%m', d). strftime returns a string, which
doesn't match any condition in the CASE statement, so the statement
produces NULL, and then the whole expression becomes NULL. To avoid
this, the result of strftime needs to be converted to integer.
And fix smart quotes ‘’ to plain apostrophe '
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
