Thank you so much Jon and Igor! Both are wokred!! Thanks a ton, JP
----- Original Message ---- From: Jon Dixon <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Saturday, July 26, 2008 6:12:21 AM Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP Alternatively, enclosing the numbers in single quotes ( ... when '0' then 'SUN' when '1' then 'MON' ...) will give you the expected result. Jon -----Inline Message Follows----- Igor Tandetnik wrote: "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
