1

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)

4
  • 1
    Does the table [users] exist in your schema? Commented Dec 10, 2018 at 16:52
  • 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) Commented Dec 10, 2018 at 17:03
  • Edit your question and add the error and the query that's generating the error 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. Commented Dec 10, 2018 at 17:17

2 Answers 2

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())
Sign up to request clarification or add additional context in comments.

Comments

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'

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.