How to make SQL internal functions default to ISO 8061 format

Question

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)
Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 6.3 (Build 14393)

Server Collation: SQL_Latin1_General_CP1_CI_AS
Language: English (United States)

I'm using internal getdate() function to automatically fill a DateCreated column of DATE datatype.

As a matter of fact when a new product is entered I can see date values are being created in table in MM/dd/YYYY format (i.e. 3/18/2017).

Why is that? Is there a way to force getdate() to use the ISO 8061 format?

CREATE TABLE [dbo].[Products] 
(
     [ID]               INT            IDENTITY (1, 1) NOT NULL,
     [Name]             NVARCHAR (50)  NOT NULL,
     [Description]      NVARCHAR (500) NOT NULL,
     [IsDeleted]        BIT            NOT NULL,
     [IsApproved]       BIT            NOT NULL,
     [CategoryID]       INT            NOT NULL,
     [UserID]           NVARCHAR (MAX) NOT NULL,
     [DateCreated]      DATE           DEFAULT (getdate()) NULL,
     [DateExpire]       DATE           CONSTRAINT [CONSTRAINT_NAME] DEFAULT (dateadd(month,(1),getdate())) NULL,
     [DateLastModified] DATE           DEFAULT (getdate()) NULL,

     CONSTRAINT [PK_dbo.Products] 
         PRIMARY KEY CLUSTERED ([ID] ASC),
     CONSTRAINT [FK_dbo.Products_dbo.Categories_CategoryID] 
         FOREIGN KEY ([CategoryID]) REFERENCES [dbo].[Categories] ([ID])
);

What you ae seeing with date, guids, floats is not what is actually stored in the database. When you see A1234-B83A-3FE190-... in a Guid or "Mar-15-2017" in a date it does not actually store the string you are seeing on the screen. You "fix" the problem at read time like SqlZim shows below. The fact that you asked the question points out that you are thinking the problem through correctly, you just did not know that Sql Server was punking you. — Sql Surfer
As of SQL Server 2008, you should use SYSDATETIME() instead of GETDATE() - and the DATE datatype does not have any format when stored - it's a 3-byte binary value in the table. The formatting only comes into play when you're displaying the value in e.g. Management Studio or an application of yours — marc_s

SqlZim SqlZim · Accepted Answer · 2017-03-19T13:54:38

The value of date and datetime data type is not stored with format in sql server. If you want to see the date in a different format you can manipulate the way that date and datetime data types are displayed when converted to a varchar (or nvarchar,nchar,char) data type using some built in functions.

Most often with convert() styles

select convert(varchar(10),getdate(),120)

returns: 2017-03-19

In sql server 2012+ you can use format()

select format(getdate(),'yyyy-MM-dd')

returns: 2017-03-19

But format() can be slower, take a look here: format() is nice and all, but… - Aaron Bertand

How to make SQL internal functions default to ISO 8061 format

2 Answers