T-SQL Date format in create table query

Question

My question: is it possible to declare date format in T-SQL during creating a table?

CREATE TABLE [dbo].[Post] 
(
    [Id]       INT IDENTITY (1, 1) NOT NULL,
    [Name]     VARCHAR(MAX) NULL,
    [RowNo]    INT  NULL,
    [ColNo]    INT  NULL,
    [Deadline] DATE NULL,

    CONSTRAINT [PK_KtoCo] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
);

It is a code from VS 2017 table designer.

For this moment date shows also time in 00:00 format. I only want dd/mm/yyyy.

Thanks for help

Presumably you're asking about `Deadline` which is declared as a `Date`. It has no time component, i.e. it is not a `DateTime`. How are you displaying the data? That is where the time is being added. — HABO, Nov 04 '18 at 23:26

score 0 · Answer 1 · answered Nov 04 '18 at 22:13

No, but you can add a computed column so you can see it as you like on output:

CREATE TABLE [dbo].[Post] (
    [Id]       INT           IDENTITY (1, 1) NOT NULL,
    [Name]     VARCHAR (MAX) NULL,
    [RowNo]    INT           NULL,
    [ColNo]    INT           NULL,
    [Deadline] DATE          NULL,
    Deadline as (convert(varchar(255), deadline, ?))
    CONSTRAINT [PK_KtoCo] PRIMARY KEY CLUSTERED ([Id] ASC)
);

The ? is for your preferred date format.

score 0 · Answer 2 · answered Nov 05 '18 at 06:58

No, you can't. Date and time data types are not stored with a display format,
only string representations of Date/Time have display formats.

You can add a computed column as Gordon Linoff suggested in his answer - you can even mark it as persisted (meaning it will be actually saved as data instead of re-calculate each time you use it) - but I'm not so sure that's a good idea - the database should not be bothered with presentation issues, and date/time string representation format is most definitely a presentation issue. This is something I would leave to the presentation layer.

The golden rule is - as long is you don't need a human to read it, it's best to keep it in the original data type - and that's especially correct when dealing with a complex issue like dates.
Moreover, the format you want is ambiguous - meaning unless you know in advance that it's dd/mm/yyyy you can't tell if 01/04/2018 is July 1st or January 4th - and computers use the local settings to figure this stuff out - so if you where to pass date between your database to your front-end application as a string, and you need some calculations on it on the way (like computing how much time until the next holiday, for instance) - you might get wrong results due to the ambiguous nature of the string representation format.

score 0 · Answer 3 · answered Nov 05 '18 at 07:03

Dates are stored in an internal format. Formats only make sense for input and output. You can include the formatted date as a separate column:

SQL Server supports the date format, Therefore in your case(dd/mm/yyyy) you have to use the below date format(103)

With century (yyyy) | Standard | Input/Output

 103                 |  British/French |  103 = dd/mm/yyyy

You can use the below-modified query

CREATE TABLE [dbo].[Post] 
(
    [Id]       INT IDENTITY (1, 1) NOT NULL,
    [Name]     VARCHAR(MAX) NULL,
    [RowNo]    INT  NULL,
    [ColNo]    INT  NULL,
    [Deadline] (CONVERT(VARCHAR(255), dt, 103)),  -- Include the formatted date as a separate column

    CONSTRAINT [PK_KtoCo] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
);

score 0 · Answer 4 · answered Nov 07 '18 at 11:04

0

Thank You guys for everything.

My solution is: I want this date only for reading. There is absolutely no logic with it, so I made column Date with varchar type. In model I declared this value as dateTime, so user pick it from datepicker. It is displayed as I wanted it to be.

Thanks!

answered Nov 07 '18 at 11:04

Patrycja

1

I'm sorry to say, but you've chosen what seems to be the worst possible solution. Not only your database does not have any way to protect you from bad data (nothing is stopping anyone from inserting a record with "Banana" in the "date" column, providing they can connect to the database directly), it also use more storage size, you will not be able to sort directly using this column, and there are probably even more bad side effects I didn't mention. – Zohar Peled Nov 08 '18 at 07:32

T-SQL Date format in create table query

4 Answers4