Operand data type bit Error using Aggregate function on BIT data type Column

[Mittle]

Can someone please help

1.getting the following error when running the following Query

Error:

Operand datatype bit is invalid for min operator .

I have googled and suggestion were to use CASE WHEN or CAST as tinyint. just wondering which is better and why as both seem to work but not sure which one is more efficient

SELECT
*
Min(Quarters.CurrentQtr) as MinofCQtr
FROM Quarters

2.

CurrentQtr Column values are below
0
0
0
0
1

 

[pbaldy]

If I had control over the table, I'd change the data type. Failing that, I'd use CAST, though just because it's simpler. The CASE would seem more appropriate when you have different options to deal with. In this case you know the problem so you don't really need to test/handle different options.

 

[Minty]

I suspect that Case is marginally more efficient if you used it like

MIN(CASE WHEN CurrentQtr = 1 Then 1 Else 0 End) As MinOfCQtr

But I'm guessing without doing some more googling.

 

[Pat Hartman]

I'm with Paul. Why fight city hall. Change the data type if you can.

 

[Mittle]

I'm with Paul. Why fight city hall. Change the data type if you can.

I'm with Paul. Why fight city hall. Change the data type if you can.

First of All Thank you for responding
Well the data type in Access database is Yes/No . as part of migrating to SQL Server , the datatypes have been changed to BIT using SSMA.
so this is a Migration Project and I have been tasked with rewriting all the Access queries so they can work efficiently in SQL Server

am not sure what you mean by change the datatype? Can you explain or suggest please.

Thanks

 

[pbaldy]

In SQL Server, you should be able to change the data type of the field from bit to tinyint. That way you don't have to work around the data type.

 

[pbaldy]

If not clear, that means using SSMS and either get the table in design view or use DDL SQL, whichever you're comfortable with.

 

[Mittle]

If not clear, that means using SSMS and either get the table in design view or use DDL SQL, whichever you're comfortable with.

oh ok didn't realise thats what you mean.
of course I know how to change datatypes .

I suppose my question was more why the need to change the datatype since the values in the database are either a 1 or 0 .dont you think this will break the reports somewhere from the users perspective

anyway thanks.

 

[pbaldy]

Well, you'd change the data type if you wanted to use Min/Max/Sum on the field. As you've found, you can't do it with a bit field (without a workaround). I have a client who had a bit field they wanted to sum. I changed the data type as described here. The field can still be represented by a checkbox and will still save 1/0. I can't recall if there were any other tweaks I had to do offhand.

 

[Mittle]

OK Thank you so much for your help plus ALL that have contributed .

 

[pbaldy]

Happy to help!

 

[Pat Hartman]

SSMA has default conversion options. YOU can change the defaults and I do. I change them because the default ODBC driver that ships with Access is ancient and doesn't include newer data types. The SSMA default for date converts to one of the "new" data types and it is easier to just change the default than to distribute the new ODBC driver that all users will need if I follow the SSMA "suggestions".