[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Convert the CURRENT_TIMESTAMP
From:       Joanne Pham <joannekpham () yahoo ! com>
Date:       2008-07-27 18:12:02
Message-ID: 149078.72711.qm () web90301 ! mail ! mud ! yahoo ! com
[Download RAW message or body]

Thanks Igor! It worked.
JP




----- Original Message ----
From: Igor Tandetnik <itandetnik@mvps.org>
To: sqlite-users@sqlite.org
Sent: Friday, July 25, 2008 9:21:26 PM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP

"Joanne Pham" <joannekpham@yahoo.com>
wrote in message news:914118.203.qm@web90301.mail.mud.yahoo.com
> 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

[prev in list] [next in list] [prev in thread] [next in thread]