CODEWITHSUNDEEP
sqlsql-servert-sql
SBB
SBB

Reputation: 8970

TSQL if no data then NULL on date column

I have an application where there are multiple date fields that can be edited by an Admin when needed. The query updates every field in the table updating it with any new information.

By default, my 3 date fields are empty in the table. When I run my update query, even though I don't add a date it updates the column to:

1900-01-01 00:00:00.000

My update statement is here:

UPDATE tuitionSubmissions
SET reimbursementDate = COALESCE (@reimbursementDate, NULL),
    empGradDate       = COALESCE (@gradDate, NULL),
    payPeriod         = COALESCE (@payPeriod, NULL),
    [status]          = @status,
    notes             = @notes,
    managerApproval   = @empID,
    approvalDate      = COALESCE (GETDATE(), NULL)
WHERE id = @tid;`

From my understanding, I thought that if those variables are empty then it would just keep the field NULL.

Is there another way to do this making it so the date field remains NULL until an actual date / value is sent to the stored procedure?

Upvotes: 0

Views: 549

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Your usage of COALESCE is meaningless. It same as IF @reimbursementDate IS NULL THEN NULL. Use it like this:

SET reimbursementDate = COALESCE (@reimbursementDate, reimbursementDate)

In this case the column will stay unchanged if parameter not passed (i.e. parameter is NULL)

Upvotes: 2

Related Questions