0

i am getting DATE_FORMAT error in ruby on rails with sql server as a database

<% date = Date.today %>
<% @employees = Employee.where("status = ? AND DATE_FORMAT(date_of_birth,'%d/%m') = ?", "Active" , date.strftime('%d/%m')) %>
Mahesh Sharma
  • 221
  • 1
  • 13
  • i am getting the above error.so what's the specific format for sql server. – Mahesh Sharma Apr 07 '17 at 06:14
  • If you google _t-sql format date_ you will fin a multitude of answers. You should try to keep all data types date though - don't convert to string because you run into trouble. – Nick.Mc Apr 07 '17 at 06:16
  • will you convert mine above rails query to sql server supported rails query??? – Mahesh Sharma Apr 07 '17 at 06:18
  • Use `FORMAT`, not `DATE_FORMAT`. See here for valid format strings: https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql. I don't know what %d/%m is. Lastly: Its a *bad* idea to fiddle about with date formats. You should keep everything a date and not cast as strings. I don't know what parameters your `Employee.where` takes, but you should keep it a date in both your code and on the SQL Server side. – Nick.Mc Apr 07 '17 at 06:31
  • will you convert mine rails query into sql sever query for rails?? – Mahesh Sharma Apr 07 '17 at 06:40
  • because i am getting error. – Mahesh Sharma Apr 07 '17 at 06:58
  • I'm sorry I'm not going to rewrite your code for you. If you took the effort to post the error I might consider it but you're really not making any effort at all – Nick.Mc Apr 07 '17 at 07:19
  • i have put down an effort........still not able to convert my dateformat to sql server/...its giving error... – Mahesh Sharma Apr 07 '17 at 07:28
  • i tried using this @employees = Employee.where("status = ? AND FORMAT(date_of_birth,'%d/%m') = ?", "Active" , date.strftime('%d/%m')) – Mahesh Sharma Apr 07 '17 at 07:28
  • thanks for your link...but i am unable to convert it.. – Mahesh Sharma Apr 07 '17 at 07:31
  • '%d/%m' is not a valid format string for Format. I suggest you check the link again. – Nick.Mc Apr 07 '17 at 07:32
  • yaa...but how should i convert the query into sql server....coz it contains where query.....!! – Mahesh Sharma Apr 07 '17 at 07:37
  • `FORMAT(date_of_birth,'%d/%m')` is incorrect, because `'%d/%m'` is an invalid string for the FORMAT function. Try this instead: `FORMAT(date_of_birth,'dd/MM')`. If you get an error, post the error and also post the version of SQL Server. – Nick.Mc Apr 07 '17 at 07:50
  • did you also try `Employee.where("status = ? AND date_of_birth = ?", "Active" , date)`? This way you are leaving everything as dates and no formatting is required. – Nick.Mc Apr 07 '17 at 07:51
  • i have tried this now...but i am getting error....@employees = Employee.where(status: "Active" AND date_of_birth: CONVERT(date,getdate(),107) – Mahesh Sharma Apr 07 '17 at 07:55
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/140149/discussion-between-mahesh-sharma-and-nick-mcdermaid). – Mahesh Sharma Apr 07 '17 at 07:56
  • i am getting this error.....irb(main):017:0> @employees = Employee.where(status: "Active" AND date_of_birth: CONVERT(date,getdate(),107)) SyntaxError: (irb):17: syntax error, unexpected tCONSTANT, expecting ')' – Mahesh Sharma Apr 07 '17 at 07:56
  • its very urgent...will you let me know my mistake...!!!!!!! – Mahesh Sharma Apr 07 '17 at 08:11
  • i have tried................ – Mahesh Sharma Apr 07 '17 at 09:09
  • i have also tried this by removing AND Keyword.......@employees = Employee.where(status: "Active",date_of_birth: CONVERT(date,getdate(),107)) – Mahesh Sharma Apr 07 '17 at 10:05
  • There must be some kind of doco for `Employees.where` that tells you what parameters it takes – Nick.Mc Apr 07 '17 at 12:41
  • what doco?????? – Mahesh Sharma Apr 07 '17 at 13:13
  • Here I'll find it for you: http://guides.rubyonrails.org/active_record_querying.html It shows this syntax as one example: `Client.where("orders_count = ?", params[:orders])` At this stage you have tried many different syntaxes. You need to pick _one_ approach and experiment until you understand what's going and why it does or doesn't work. I can tell you in your current attempts you are mixing up T-SQL and ruby(?) syntax. Thats not going to work. The stuff inside the double quotes is T-SQL syntax. The stuff outside is ruby(?) syntax. – Nick.Mc Apr 07 '17 at 14:07
  • Here is one suggestion (but it invites SQL injection and is specifically warned against: `Employee.where("status = 'Active' AND FORMAT(date_of_birth,'dd/MM') = '" + date.strftime('%d/%m') + "'")` – Nick.Mc Apr 07 '17 at 14:10
  • Possible duplicate of [strftime error while switching my database from sqlite3 to mysql in Rails4](http://stackoverflow.com/questions/43061570/strftime-error-while-switching-my-database-from-sqlite3-to-mysql-in-rails4) – Nick.Mc Apr 09 '17 at 22:33
  • YOU ALREADY ASKED THIS! AND IT WAS ANSWERED! – Nick.Mc Apr 09 '17 at 22:33

1 Answers1

0

Try this,

<% date = Date.today %>
<% @employees = Employee.where("status = ? AND FORMAT(date_of_birth,'dd/MM','en-US') = ?", "Active" , date.strftime('%d/%m')) %>

Learn more about Format function

Hardik Upadhyay
  • 2,639
  • 1
  • 19
  • 34
  • my new question link.......answer me if you know.......... http://stackoverflow.com/questions/43320451/how-to-take-auto-backup-of-mysql-database-in-rails-4 – Mahesh Sharma Apr 10 '17 at 10:41
  • why this code is not working...........@food_deductions = FoodDeduction.where("DATE_FORMAT('%Y/%m', food_date) = ?",date.strftime('%Y/%m')) – Mahesh Sharma Apr 10 '17 at 11:59