Member Avatar for misstj555

Hello. I am trying to check if a table definition (the default value on a constraint) is equal to UTC Time by using the following query:

IF NOT EXISTS((SELECT object_definition(default_object_id) AS definition
FROM sys.columns
WHERE name = 'ModifiedDate'
AND object_id = object_id('dbo.WorkflowAudit')) == GETUTCDATE())

However, you can not compare a function (GETUTCDATE()) to this Select Statement. I need to do it this way because I am building an update script and it can only update the table IF the table definition is NOT equal to UTC TIME Any ideas? Thank you

Edited by misstj555 because: specify table definition
  • 2 Contributors
  • 2 Replies
  • 108 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by john_111

Dani AI

Generated

: the core issue is mixing two different things — the metadata string that describes a column default, and a runtime datetime value. Comparing the default's definition text to the current UTC timestamp will always fail. The reliable pattern is: read the default-expression text from the catalog, normalize that text (remove outer parentheses, trim whitespace, compare case-insensitively), and then decide whether to alter the constraint.

Recommended workflow (no verbatim SQL here): find the default object tied to the ModifiedDate column, read the catalog column that holds the default expression, and normalize the string so that variations like extra parentheses or casing do not prevent a match. Compare the normalized expression to the expected function call text (for example, getutcdate()). If it does not match, find the constraint name that owns that default (system names are common), drop that constraint, and add a new default constraint with the correct expression. Keep the ALTER/DROP statements inside a transaction and script the drop/add steps so you do not lose the column or any dependency.

A few practical cautions: changing a column default only affects future inserts — it will not change existing ModifiedDate rows. Some default definitions include additional wrapping or conversions, so prefer a normalized-string comparison rather than a raw equality or a datetime variable compare (as suggested by , capturing the current UTC into a variable will not help when you are comparing definition text). Also handle the case where no default exists (catalog id is zero) to avoid errors.

Microsoft docs that explain the catalog views and functions useful here: sys.default_constraints (Transact-SQL) (for the definition column), sys.columns (for the default id), and GETUTCDATE (Transact-SQL) (to confirm the exact function name you expect).

Member Avatar for misstj555

One thing that I tried to do is to get the constraint and see if the constraint is equal to "(getutcdate())". Here is the code for this:

SELECT object_definition(default_object_id) AS definition
                        FROM sys.columns
                        WHERE name = 'ModifiedDate'
                        AND object_id = object_id('dbo.WorkflowAudit') == '(getutcdate())'

Basically, if the column default is "(getutcdate())", it should match the string "(getutcdate())". This may be an impractical way of doing it but this is all I could think of.

Member Avatar for john_111

Have you tried getting the date first?
ie. the first line reads getutcdate() and that is assigned to a variable
then follow that with the select, assign the query result to a second variable, and compare that to the first variable.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.