"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
