Use current date as default value for a column

Adding and modifying defaults

Working with default values can be a breeze if you grasp their scope and potential to transform your schema. Deploying and adjusting default values can aid you in maintaining database integrity.

Deploying defaults with ALTER TABLE

To add or modify the default value of an existing column, use ALTER TABLE. Here are the commands for SQL Server and PostgreSQL or MySQL:

-- SQL Server
ALTER TABLE Orders ADD CONSTRAINT DF_OrderDate DEFAULT GETDATE() FOR OrderDate;

-- PostgreSQL and MySQL
ALTER TABLE Orders ALTER COLUMN OrderDate SET DEFAULT CURRENT_DATE;

-- SQL Server needs a fancier dress code for ALTERing TABLEs 🎩


Using ALTER TABLE allows you to enhance existing tables with new default values without having to recreate them from scratch.

The magic of default constraints

Applying default constraints leverages automated data entry which is particularly useful for fields like timestamps of creation. No manual SQL specification is necessary during an INSERT operation, thus enhancing data consistency and workflow efficiency.

Custom default logic using triggers

For occasions where your defaults require a bit more of a creative approach, triggers can be a handy alternative. Triggers can respond to specific database actions (like INSERT) according to predefined rules and are a powerful way of maintaining data integrity.

Tools for schema management

Visual Studio Database Projects and SQL Server Management Studio (SSMS) are excellent for managing schema changes. They offer functionalities like schema comparison and generation of changes scripts.

Playing nice with date and time types

Understanding the quirks of date and time data types is key to setting up defaults properly. The DATE, DATETIME, TIMESTAMP, and TIME types all have their personalities and operate in sometimes surprising ways.

Beware of time zones

When relying on functions like GETDATE(), remember it defaults to the server time zone. If you've got a global operation at hand, consider GETUTCDATE() for a more universal time setting.

Leap years and time changes

Ensure your application is smart enough to consider leap years and Daylight Saving Time changes. Fail to do so, and you might find your default dates jumping around like a kangaroo on espresso ☕!

Compatibility between data types and functions

Always check that your chosen default value function actually plays nice with your column's data type. A DATE column and the function GETTIME() might not go on a second date, considering it only stores the time!