strftime error while switching my database from sqlite3 to mysql in Rails4

Question

Recently i changed my database from sqlite3 to mysql.When i ran my project i am getting this error in one of the file which is using this query.

date = Date.today + 1

@employees = Employee.where("status = ? AND strftime('%d/%m', date_of_birth) = ?", "Active" , date.strftime('%d/%m')

ActionView::Template::Error (Mysql2::Error: FUNCTION hrms_development.strftime does not exist: SELECT COUNT(*) FROM `employees` WHERE (status = 'Active' AND strftime('%d/%m', date_of_birth) = '28/03')):
    210:             
    211:               <% date = Date.today %>
    212:              <% @employees = Employee.where("status = ? AND strftime('%d/%m', date_of_birth) = ?", "Active" , date.strftime('%d/%m')) %>
    213:             <% if @employees.empty? %>
    214:              
    215:             <%else%>
    216:               <% @employees.each do |e| %>
  app/views/home/_group_admin.html.erb:213:in `_app_views_home__group_admin_html_erb___2522183600721478262_91627100'
  app/views/home/index.html.erb:17:in `_app_views_home_index_html_erb__2772204906267359422_86967120'



ActionView::Template::Error: Mysql2::Error: FUNCTION hrms_development.strftime does not exist: SELECT COUNT(*) FROM `employees` WHERE (status = 'Active' AND strftime('%d/%m', date_of_birth) = '28/03')

score 1 · Accepted Answer · answered Mar 28 '17 at 06:26

1

Try this with simply replace,

<% @employees = Employee.where("status = ? AND strftime('%d/%m', date_of_birth) = ?", "Active" , date.strftime('%d/%m')) %>

With

<% @employees = Employee.where("status = ? AND DATE_FORMAT(date_of_birth,'%d/%m') = ?", "Active" , date.strftime('%d/%m')) %>

answered Mar 28 '17 at 06:26

Hardik Upadhyay

2,639
1
19
34

i am getting this error on windows when i am running my project and doing rake db:migrate – Mahesh Sharma Mar 28 '17 at 07:20
D:/Ruby21-x64/lib/ruby/gems/2.1.0/gems/activesupport-4.2.4/lib/active_support/dependencies.rb:274:in `require': Could no t load 'active_record/connection_adapters/mysql2_adapter'. Make sure that the adapter in config/database.yml is valid. I f you use an adapter other than 'mysql', 'mysql2', 'postgresql' or 'sqlite3' add the necessary adapter gem to the Gemfil e. (LoadError) – Mahesh Sharma Mar 28 '17 at 07:20
Everthing works fine on Linux ubuntu,only on windows i am getting this error. – Mahesh Sharma Mar 28 '17 at 07:21
you need to install mysql in windows machine..as well – Hardik Upadhyay Mar 28 '17 at 08:52
i am having problem while exporting my datababase in mysql, i have alo ran mysqldump -h [host] -u [uname] -p[pass] db_name > db_backup.sql then database.sql,and also source database.sql still no data in tables?????? – Mahesh Sharma Mar 29 '17 at 06:57
sorry i can't understand your issue. – Hardik Upadhyay Mar 29 '17 at 07:00
and one more thing you need to import data from .sql file currently you are exporting data – Hardik Upadhyay Mar 29 '17 at 07:11
ya i am importing data....i am using mysqldump -h [host] -u [uname] -p[pass] db_name < db_backup.sql an database.sql file contains all the insert command.But on running source database.sql command 0 row affected is coming. – Mahesh Sharma Mar 29 '17 at 07:37
will you please let me know in step by step how may i copy database.sql file to database. – Mahesh Sharma Mar 29 '17 at 07:39
and get all the data in the tables. – Mahesh Sharma Mar 29 '17 at 07:39
i am getting Dump completed message,but not getting data in the table. – Mahesh Sharma Mar 29 '17 at 07:46
first drop and recreate your database then after run this command mysql -u username -p database_name < database.sql – Hardik Upadhyay Mar 29 '17 at 08:27
but database.sql is the file which i have tken from another pc......and i have placed it in db folder.....for that file i need to create dump....and insert all enteries in database. – Mahesh Sharma Mar 29 '17 at 08:40
i am successful in copying mysql database...will you upvote me...so that i may increase my reputatiion here... – Mahesh Sharma Mar 29 '17 at 10:52
in athis question you can upvote me............http://stackoverflow.com/questions/43088609/replace-mysql-database-from-one-computer-to-other-in-rails-as-we-do-in-sqlite3/43088706?noredirect=1#comment73263572_43088706 and also on other questions....!!!!!! – Mahesh Sharma Mar 29 '17 at 11:01
i have posted one question...help me....http://stackoverflow.com/questions/43109523/how-may-i-switch-my-rails-app-from-mysql-to-sql-server-in-rails-4-in-windows @Hardik – Mahesh Sharma Mar 30 '17 at 05:59

score 0 · Answer 2 · edited May 23 '17 at 12:25

0

There is no strftime function in MySQL. Use DATE_FORMAT instead.

See Convert DateTime Value into String in Mysql for more examples.

edited May 23 '17 at 12:25

Community

1
1

answered Mar 28 '17 at 06:02

Alexander Ushakov

5,139
3
27
50

Will you please provide me an example of how may i apply DATE_FORMAT in rails 4.I am new to this so don't know. – Mahesh Sharma Mar 28 '17 at 06:07
date = Date.today @employees = Employee.where("status = ? AND strftime('%d/%m', date_of_birth) = ?", "Active" , date.strftime('%d/%m')) – Mahesh Sharma Mar 28 '17 at 06:19
i want to convert this query to mysql. – Mahesh Sharma Mar 28 '17 at 06:19
my new question.............http://stackoverflow.com/questions/43065691/mysql-error-while-running-rake-dbmigrate-in-windows @hardik – Mahesh Sharma Mar 28 '17 at 10:22

strftime error while switching my database from sqlite3 to mysql in Rails4

2 Answers2

Linked