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.
Leave a Reply