0

I have a query that I'm changing from running on Access to being passed through to SQL, and it's tripping errors that it never did before.

One possibility that I thought of is that I'm not sure if some of my conditional statements work the same in SQL Server as they do in Access. For instance, in my existing query I test to see if a date has been entered into a field called Closed with a statement like the following:

WHERE c.Closed

In Access this returns true whenever anything is entered in Closed, and false otherwise. Does SQL Server work the same way? If not, is there an elegant way to test the same thing, or do I have to explicitly test for emptiness and nullness?

This doesn't seem to be exactly the same as a boolean literal (for which an answer already exists), as those are literally valued "True" or "False". Here I'm comparing to the behavior in Access where some data types which aren't zero length, zero (value) or null evaluate to True when used in a conditional. Microsoft describes this as data types which are "implicitly convertible to boolean" within conditional statements. I thought this was different than a literal. Or at least, it's unclear enough that I think a separate question is appropriate.

Emily Beth
  • 709
  • 1
  • 7
  • 23
  • 3
    Perhaps you want something like `WHERE c.Closed IS NOT NULL` or maybe `WHERE c.Closed IS NULL`. Add sample data to your question. – Tim Biegeleisen Sep 16 '18 at 02:58
  • 3
    Possible duplicate of [SQL Server - boolean literal?](https://stackoverflow.com/questions/7170688/sql-server-boolean-literal) – ashleedawg Sep 16 '18 at 03:01
  • Whatever it is (boolean literal or otherwise), I realized I could try this in a simpler query directly in SQL Server Management Studio. There I get a red underline syntax error on the "c.Closed" statement, and it says "An expression of non-boolean type specified in a context where a condition is expected, near ';'." So I think that answers my question that I do indeed need an explicit conditional. Any insight into my 2nd question then: What is the most elegant way to check whether a proper date has been entered into a date field? – Emily Beth Sep 16 '18 at 03:10
  • Thanks Tim, I didn't see your comment before I sent my last. I did a bit of testing around in SSMS, and it seems that if I delete a previously entered date, then the DB changes the cell to Null. I don't think that was (always) the case in Access, some dates just appeared as zero length strings if they were entered and then deleted, so just checking for nullness wasn't safe. Anyone know if that is DEFINITELY the case in SQL Server: that deleted dates ALWAYS revert to being Nulls? – Emily Beth Sep 16 '18 at 16:56
  • Another update: I did some testing in my Access database, and I think my assumptions there were wrong also. In the Access DB the only values for date fields are actual dates or Nulls, even if a user deletes the item (I tested it). I also tested using SQL to replace an Access date field with a zero length string, and got a "type conversion failure." It wouldn't do it. I've tried to find documentation on this for Access of SQL Server, but couldn't find anything that says this explicitly, but my testing seems to indicate there are no ZLSs in date fields. – Emily Beth Sep 16 '18 at 18:00

1 Answers1

1

Your statement is invalid in sql server. You can have WHERE c.Closes IS NOT NULL or you can use ISNULL command to replace the null values with something else.

krezaeim
  • 170
  • 1
  • 7