CODEWITHSUNDEEP
sqlsql-server
Luke
Luke

Reputation: 409

How to make SQL internal functions default to ISO 8061 format

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)

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])
);

Upvotes: 2

Views: 180

Answers (2)

SqlZim
SqlZim

Reputation: 38063

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

Upvotes: 7

Mark Schultheiss
Mark Schultheiss

Reputation: 34217

The format is not set in the data as stored for the date data types.

You can however set the format to be used for the visual display of dates.

SET DATEFORMAT { format | @format_var }

--  For example set date format to day/month/year.  
SET DATEFORMAT dmy;  
GO

format | @format_var

Is the order of the date parts. Valid parameters are mdy, dmy, ymd, ydm, myd, and dym.

Note ydm is not supported for date, datetime2 and datetimeoffset data types.

Just to add clarity this differs from the date and time styles format as as varchar for instance in:

CONVERT(VARCHAR(20), GETDATE(), 100)

Upvotes: 0

Related Questions