CODEWITHSUNDEEP
sql-serverdatabasedatabase-design
Brandon Linton
Brandon Linton

Reputation: 4411

How can I drive a boolean field in SQL Server off of a nullable column?

I'd like to have two columns in a database, one for tracking whether or not the user has submitted something, and another for the timestamp of that submission.

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

Basically, I'd like the boolean field to be driven by whether or not a SubmittedDate column is null. Here's 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!

Upvotes: 2

Views: 422

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294307

Use a computed column:

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

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332591

Use only one column - the DATETIME one. It serves double duty - the column being null means it wasn't submitted, but if the value exists - you also know when.

Upvotes: 6

Related Questions