Have Geek Brain

How can I manage a boolean field in SQL Server with a null column?

I would like to have two columns in the database, one to keep track of whether the user has submitted anything and the other to timestamp that view.

How can I structure my table definition so that the state of those two columns is never inconsistent?

Basically, I would like the boolean field to be controlled by whether the SubmittedDate column is null. Here is a snippet of the table definition:

CREATE TABLE SomeSchema.SomeTable 
(
    ...
    SubmittedDate                   datetime                NULL,
    Submitted                       bit                     NOT NULL        DEFAULT(0), -- Drive off of SubmittedDate?
    ...

)

What's the best way to accomplish this?

Thanks!

+2


a source to share


2 answers


Use a calculated column :



CREATE TABLE SomeSchema.SomeTable 
(
    ...
    SubmittedDate                   datetime                NULL,
    Submitted      as cast(case when SubmittedDate is null then 0 else 1 end as bit)
)
+2


a source


Use only one column - DATETIME. It caters for double duty - a column being zero means it was not sent, but if the value exists - you also know when.



+6


a source






More articles:


All Articles