How to get date format in result set?

Question

I have a sql to get data form mysql their I used dateformat to convert date required format Here is the sql

SELECT 
 m.id,
 m.subject,
 m.body,
 m.read,
 m.hide,
 m.thread_id,
 DATE_FORMAT(m.sent_date, '%d-%b-%Y') sent_date,
 u.username to_name
 FROM 
 messages m,
 users u
 WHERE 
 u.school_id = m.school_id AND
 u.id = m.to_user_id AND
 m.school_id = 18 AND
 m.user_id = 53 AND
 m.status = 'sent'
 ORDER BY m.sent_date DESC

When I run this sql directly in mysql it works fine, sent_date comes as (19-Apr-2010). When I excute this in ruby, in result set the sent_date is stores as (Tue Apr 20 00:00:00 UTC 2010)

messages = Message.find_by_sql(sql)
puts messages[0]['sent_date']

puts statement prints "Tue Apr 20 00:00:00 UTC 2010"

how to get same date format in mysql result and ruby result set.

Thanks

Answer 1

It is not string, but date object, if you use some date column type for storing this field in your database. You can use strftime method for output formating. For 19-Apr-2010 format you can use this

puts messages[0]['sent_date'].strftime("%d-%b-%Y")

Answer 2

You Froget to use "AS" in your query so it is taking default datatype set in your database.

Following should work. Cheer's

DATE_FORMAT(m.sent_date, '%d-%b-%Y') AS sent_date

EDITED for "AS keyword is optional"

I check in my own code & i came to following conclusion

when i write something like follwing
1] column name is same and w/o "AS"

   @check = DATE_FORMAT(m.sent_date, '%d-%b-%Y') sent_date
   @check[0].sent_date >>  Mon, 22 Mar 2010 00:00:00 UTC +00:00

2] column name is same and with "AS"

   @check = DATE_FORMAT(m.sent_date, '%d-%b-%Y') AS sent_date
   @check[0].sent_date >>  22-Mar-2010

3] column name is different and w/0 "AS"

   @check = DATE_FORMAT(m.sent_date, '%d-%b-%Y') date
   @check[0].date >>  22-Mar-2010