SQL Date type with format

Question

I need this format for my data 'MM-dd-yyyy hh:mm tt'

CREATE TABLE [dbo].[Bills] 
(
    [Ordernum]  INT       IDENTITY (1, 1) NOT NULL,
    [BillDate]  DATETIME  NOT NULL,
    [ItemsID]   INT       NOT NULL,
    [ClientID]  INT       NOT NULL,
    [PrisNoB]   FLOAT(53) NOT NULL,
    [PrisWithB] FLOAT(53) NOT NULL,

    PRIMARY KEY CLUSTERED ([Ordernum] ASC),

    CONSTRAINT [FKClient] 
         FOREIGN KEY ([ClientID]) REFERENCES [dbo].[Client] ([Id]),
    CONSTRAINT [FKItems] 
         FOREIGN KEY ([ItemsID]) REFERENCES [dbo].[Items] ([Id])
);

So where can I define the date format MM-dd-yyyy hh:mm tt?

I tried to do it like this:

[BillDate] DATETIME ('MM-dd-yyyy hh:mm tt') NOT NULL,

but I get an error.

Not how it works in SQL. Convert to a string when you fetch the data. The database stores the value using an internal format. — Gordon Linoff
– Gordon Linoff, Commented Oct 11, 2019 at 2:49
@GordonLinoff its not nice to keep converting the date to string and from string to Date every time i need to use it — RYNEX Akil
– RYNEX Akil, Commented Oct 11, 2019 at 3:42
Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Oct 11, 2019 at 3:50

Michał Turczyn · Accepted Answer · 2019-10-11 05:22:53Z

1

As said, SQL stores date in its internal format, so all you can do is specify datatype, which would be datetime. There's no possibility to change format of the date, in which it will be stored (afer all, why do you think it matters for the computer what format it's in? It's just bunch of numbers contaning information about date...).

The best you can do, is to define view, which will use format function. See FORMAT (Transact-SQL)

Then use that view instead of table.

Another way is to write query, which will return date in desired format, again using format function.

One more option is, since you specified WPF tag, is to get data to a program and handle formatting in presentation layer, i.e. in you program. This is, IMO, the best option, because it keeps fine separation between data layer and presentation layer.

answered Oct 11, 2019 at 5:22

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-10-11 10:42:31Z

0

The best you can do is not a view but a computed column. You can add a column which calculates the formatted value each time as:

alter table bill add billdate_formatted as (format(billdate, 'MM-dd-yyyy hh:mm tt'));

You can store this value once by making the column persisted:

alter table bill add billdate_formatted as (format(billdate, 'MM-dd-yyyy hh:mm tt')) persisted;

Both of these guarantee that billdate_formatted is always in synch with billdate.

I don't recommend persisted. That is usually used when you want to use the computed column in an index (although there are other situations as well).

answered Oct 11, 2019 at 10:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Date type with format

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related