Laravel MySQL to SQL Server

Question

We have developed project in Laravel with Mysql want to connect the project to SQL Server database. The project is not able to connect to SQL Server - we're getting the following error:

SQLSTATE[22007]: [Microsoft][ODBC Driver 11 for SQL Server]
[SQL Server]Conversion failed when converting date and/or time from character string. (SQL: select sum([credit]) as aggregate from [loan_transactions] where [transaction_type] = repayment and [reversed] = 0 and [date] between date_sub(now(),INTERVAL 1 WEEK) and now())
(View: D:\Xampp\htdocs\loanmanager\resources\views\dashboard.blade.php)

yes now i am getting the following error SQLSTATE[22007]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string. (SQL: select sum([credit]) as aggregate from [loan_transactions] where [transaction_type] = repayment and [reversed] = 0 and [date] between date_sub(now(),INTERVAL 1 WEEK) and now()) (View: D:\Xampp\htdocs\loanmanager\resources\views\dashboard.blade.php) — Neo Udaipur
– Neo Udaipur, Commented Dec 10, 2018 at 17:03
Edit your question and add the error and the query that's generating the error — WillardSolutions
– WillardSolutions, Commented Dec 10, 2018 at 17:06
it looks like you've connected to the database and it has tried to run an SQL statement - my best guess is that the SELECT statement isn't compatible with SQL Server, things like date_sub or now() (I'm used to using getdate() instead) may not be recognised SQL Server commands. — Phill
– Phill, Commented Dec 10, 2018 at 17:17

Peter · Accepted Answer · 2018-12-10 18:28:59Z

2

It looks like you are trying to run a query with MySQL specific syntax that is not compatible with MSSql. Instead of using the date_sub() MySQL function, try using the MSSql equivalent:

... and [date] between DATEADD(week, -1, GETDATE()) and GETDATE())

edited Dec 10, 2018 at 18:28

answered Dec 10, 2018 at 18:23

Peter

1,7351 gold badge11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jane · Accepted Answer · 2018-12-10 22:58:52Z

0

Not an answer, just can't comment. Migrations can be a fuzz as well when it comes to supporting both MySQL and MSSQL, not just with FK and indexes but a lot of stuff. Just a tip, if you're using an if-statement to support both, this can be one way env('DB_CONNECTION') === 'sqlsrv'

answered Dec 10, 2018 at 22:58

Jane

4383 silver badges10 bronze badges

Collectives™ on Stack Overflow

Laravel MySQL to SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related