Disallowed implicit conversion from data type smalldatetime to data type float
-
I took a PHP job, upgrading an old PHP 4.7 project to PHP 7.14. I'm doing pretty good rewriting it as an object oriented app, and re imagining the design. But I'm terrible at SQL, and glad Linq came along. I get the error message below, and played around with the statement, ruling out that the cast is the issue. I think the date I put in bold is the issue, because I removed the line and it ran fine. I get the convert part, but should I convert to a float as well? Why a float if so? Is the date really stored as numbers?
Error Message:
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 260 [code] => 260 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Disallowed implicit conversion from data type smalldatetime to data type float, table 'commission_summary', column 'startup_check_date'. Use the CONVERT function to run this query. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Disallowed implicit conversion from data type smalldatetime to data type float, table 'commission_summary', column 'startup_check_date'. Use the CONVERT function to run this query. ) )getSalesBonusByDate 362: SELECT count (a.project_no) FROM project as a, commission_summary as b WHERE a.project_no = b.project_no AND (a.sold_date <= 3-1-2021) AND (a.status = 'construction' or a.status = 'finished') AND ((CAST(FLOOR(b.startup_check_date)AS DATETIME) BETWEEN 1-1-2021 AND 3-1-2021)) AND a.sales_no = '79' 1
Query: I think the date in bold is the error, but I'm not sure what to convert the value to. The field is a smalldatetime.
SELECT count (a.project_no) FROM project as a, commission_summary as b WHERE a.project_no = b.project_no AND (a.sold_date <= 3-1-2021) AND (a.status = 'construction' or a.status = 'finished') AND ((CAST(FLOOR(b.startup_check_date)AS DATETIME) BETWEEN 1-1-2021 AND 3-1-2021)) AND a.sales_no = '79'
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
I took a PHP job, upgrading an old PHP 4.7 project to PHP 7.14. I'm doing pretty good rewriting it as an object oriented app, and re imagining the design. But I'm terrible at SQL, and glad Linq came along. I get the error message below, and played around with the statement, ruling out that the cast is the issue. I think the date I put in bold is the issue, because I removed the line and it ran fine. I get the convert part, but should I convert to a float as well? Why a float if so? Is the date really stored as numbers?
Error Message:
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 260 [code] => 260 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Disallowed implicit conversion from data type smalldatetime to data type float, table 'commission_summary', column 'startup_check_date'. Use the CONVERT function to run this query. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Disallowed implicit conversion from data type smalldatetime to data type float, table 'commission_summary', column 'startup_check_date'. Use the CONVERT function to run this query. ) )getSalesBonusByDate 362: SELECT count (a.project_no) FROM project as a, commission_summary as b WHERE a.project_no = b.project_no AND (a.sold_date <= 3-1-2021) AND (a.status = 'construction' or a.status = 'finished') AND ((CAST(FLOOR(b.startup_check_date)AS DATETIME) BETWEEN 1-1-2021 AND 3-1-2021)) AND a.sales_no = '79' 1
Query: I think the date in bold is the error, but I'm not sure what to convert the value to. The field is a smalldatetime.
SELECT count (a.project_no) FROM project as a, commission_summary as b WHERE a.project_no = b.project_no AND (a.sold_date <= 3-1-2021) AND (a.status = 'construction' or a.status = 'finished') AND ((CAST(FLOOR(b.startup_check_date)AS DATETIME) BETWEEN 1-1-2021 AND 3-1-2021)) AND a.sales_no = '79'
If it ain't broke don't fix it Discover my world at jkirkerx.com
try '3-1-2021' rather than 3-1-2021
-
try '3-1-2021' rather than 3-1-2021
Good idea and tried it, but I think I need to study convert and learn it really fast.
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
try '3-1-2021' rather than 3-1-2021
-
SQL Server uses ISO dates, so it should be '2021-03-01' (if you are wanting 1st March) or '2021-01-03' (for 3rd January) - the quotes are needed
Ok ... The dates stored in the database are like 3/1/2021 as smalldatetime But I'm asking to compare against 2021-03-01, an ISO date, so wrap what I'm asking to compare to. I came up with this ...
$query = "
SELECT
count (a.project_no)
FROM project as a, commission_summary as b
WHERE a.project_no = b.project_no
AND (a.sold_date <= '$lastDate')
AND (a.status = 'construction' or a.status = 'finished')
AND (convert(CHAR(10), b.startup_check_date, 120) BETWEEN '$firstDate' AND '$lastDate')
AND a.sales_no = '$salesId'";I fiddled with the conversion, and was sure I got it right but it failed. Then I used the suggestion to wrap the dates in single quotes and it produced a clean result of 9. I get the convert part, declare a CHAR no more than 10, input value, ? Not sure what the 120 stands for. However your explanation of the date formats has schooled me in how to fiddle with them and get it right. Just FYI, I wrote some other fixes earlier that are similar, and just had a complete blackout of my previous experiences. But this lesson should solidify it. Thanks!
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
Ok ... The dates stored in the database are like 3/1/2021 as smalldatetime But I'm asking to compare against 2021-03-01, an ISO date, so wrap what I'm asking to compare to. I came up with this ...
$query = "
SELECT
count (a.project_no)
FROM project as a, commission_summary as b
WHERE a.project_no = b.project_no
AND (a.sold_date <= '$lastDate')
AND (a.status = 'construction' or a.status = 'finished')
AND (convert(CHAR(10), b.startup_check_date, 120) BETWEEN '$firstDate' AND '$lastDate')
AND a.sales_no = '$salesId'";I fiddled with the conversion, and was sure I got it right but it failed. Then I used the suggestion to wrap the dates in single quotes and it produced a clean result of 9. I get the convert part, declare a CHAR no more than 10, input value, ? Not sure what the 120 stands for. However your explanation of the date formats has schooled me in how to fiddle with them and get it right. Just FYI, I wrote some other fixes earlier that are similar, and just had a complete blackout of my previous experiences. But this lesson should solidify it. Thanks!
If it ain't broke don't fix it Discover my world at jkirkerx.com
I am not sure why you are converting to CHAR(10) when you are looking for a date comparison/ What you are converting is your b.startup_chack_date value. If that is already a date then it does not need converting. For the meaning of the 120 (and other values) see, for example, <a href="https://www.w3schools.com/SQL/func_sqlserver_convert.asp">[^]