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, Mar 19 '17 at 13:59
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, Mar 19 '17 at 14:41

SqlZim · Accepted Answer · 2017-03-19T14:15:21.743

7

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

edited Mar 19 '17 at 14:15

answered Mar 19 '17 at 13:54

SqlZim

37,248
6
41
59

Further reading: [Sql server does not store date display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) – Zohar Peled Mar 19 '17 at 13:58
Thanks Aaron, I am more confused than ever now.. As I have to compare inserted date with today, if I understand well I should convert stored data to ISO 8061, convert DateTime.Today to string, compare the two strings to check which is greater (but then are no more dates now... so how can it be done?). Moreover as I am using Linq here's my select: DateTime today = DateTime.Today.Date; from p in db.Products where (p.IsDeleted == false && p.IsApproved == true && p.DateExpire > today) select p; How to use 'convert' in that case? – Luke Mar 19 '17 at 14:14
@Luke I'm not Aaron, that's the author of the article in the link. – SqlZim Mar 19 '17 at 14:15
1

@Luke do not pass datetime as a string, pass it as a date parameter and do the comparison without any conversions. Here is how to truncate time for your linq query: http://stackoverflow.com/a/19935487/2333499 – SqlZim Mar 19 '17 at 14:16
1

Thanks SqlZim and sorry for the name mismatch. You made it very clear now. As I got few results due to poor testing data pool I wrongly deduced it was caused by wrong data formatting at SQL Server level. That was not the case and you helped me put it all together. Thanks again. – Luke Mar 19 '17 at 14:51

Mark Schultheiss · Answer 2 · 2017-03-19T14:08:42.123

0

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)

edited Mar 19 '17 at 14:08

answered Mar 19 '17 at 14:02

Mark Schultheiss

32,614
12
69
100

This "Sets the order of the month, day, and year date parts for **interpreting** ... strings" not display format. Display format is determined by the client. – Martin Smith Mar 19 '17 at 14:22

How to make SQL internal functions default to ISO 8061 format

2 Answers2