How to set default value for a datetime column in SQL Server to a constant

I had the need today, to set the default value for a datatime column in SQL Server to something else than GETDATE(). In my case, I wanted a default value of 2014/01/01. I could not easily find info on what is allowed as a default value, when you design the table in SSMS or write a DDL query yourself to create a new table.

The answer is that you can enter an expression that evaluates to – or can be cast to – a datetime value, such as:

GETDATE(). 

‘2014-01-01’.  This string can be cast to a datetime successfully.

CONVERT(datetime, ‘2014-01-01’, 111). You have more control here. The format of the input string is specified and an explicit conversion is done.

Here is a more complete list of the various datetime formats that can be used in the latter case:

SELECT convert(varchar, getdate(), 100) -- Oct 23 2016 10:22AM (or PM)
SELECT convert(varchar, getdate(), 101) -- 10/23/2016
SELECT convert(varchar, getdate(), 102) -- 2016.10.23
SELECT convert(varchar, getdate(), 103) -- 23/10/2016
SELECT convert(varchar, getdate(), 104) -- 23.10.2016
SELECT convert(varchar, getdate(), 105) -- 23-10-2016
SELECT convert(varchar, getdate(), 106) -- 23 Oct 2016
SELECT convert(varchar, getdate(), 107) -- Oct 23, 2016
SELECT convert(varchar, getdate(), 108) -- 09:10:34
SELECT convert(varchar, getdate(), 109) -- Oct 23 2016 11:10:33:993AM (or PM)
SELECT convert(varchar, getdate(), 110) -- 10-23-2016
SELECT convert(varchar, getdate(), 111) -- 2016/10/23
SELECT convert(varchar, getdate(), 112) -- 20161023
SELECT convert(varchar, getdate(), 113) -- 23 Oct 2016 06:10:55:383
SELECT convert(varchar, getdate(), 114) -- 06:10:55:383(24h)
SELECT convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h)
SELECT convert(varchar, getdate(), 121) -- 2016-10-23 06:10:55.383
SELECT convert(varchar, getdate(), 126) -- 2016-10-23T06:10:55.383

For additional details, see this article on how to convert from string to datetime.