TSQL if no data then NULL on date column

Question

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?

Is the field nullable? You can check with SELECT COLUMNPROPERTY(OBJECT_ID('TableName', 'U'), 'ColumnName', 'AllowsNull'); — Bob Tway
– Bob Tway, Commented May 21, 2014 at 14:30
Looks like the client code is passing a non-null value of zero (0) to the update statement. Try changing the coalesce statements to : NullIf(@reimbursementDate, '1 jan 1900') — Charles Bretana
– Charles Bretana, Commented May 21, 2014 at 14:35

Hamlet Hakobyan · Accepted Answer · 2014-05-21 14:33:40Z

2

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)

answered May 21, 2014 at 14:33

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SBB Over a year ago

I updated it to this however as soon as I send other fields to the database, it updates those fields from NULL to 1900-01-01 00:00:00.000

SBB Over a year ago

This worked, didnt notice my vars was set as varchar and not datetime

Collectives™ on Stack Overflow

TSQL if no data then NULL on date column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related