CODEWITHSUNDEEP
sqlsql-serverdate
Mikhail_Sam
Mikhail_Sam

Reputation: 11208

SQL: how to specify a date format on creating a table and fill it

I want to save the date in format 'dd.mm.yyyy'. So I read there are different formats for a date in SQL (by the way I use Visual Studio and SQL Server).

I tried this code:

CREATE TABLE APP(
    ID   INT NOT NULL,
    DT DATE FORMAT 'dd.mm.yyyy',
    ADDRESS  NVARCHAR (100) ,
    PRIMARY KEY (ID)
);

But it returns the error:

Incorrect syntax near 'FORMAT'.

After that I want to use this code:

INSERT INTO APP (ID, DT)
VALUES ('1','22.12.2016')

Upvotes: 6

Views: 204988

Answers (6)

Prasan Karunarathna
Prasan Karunarathna

Reputation: 385

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. You have to use the below date format.

With century (yyyy) | Standard | Input/Output

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



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

Upvotes: 1

Rohit yadav
Rohit yadav

Reputation: 159

Its default setting is yyyy-MM-dd.

No, it's not. There is no formatting information at all associated with the field.

The value is not formatted by the database. It's returned only as a point in time. Formatting that value into its textual representation is done by the application that is getting the data from the database.

So, there is nothing that you can do in the database to change how the date value is formatted. You have to change that where the data is displayed.

Upvotes: 1

pankaj singh
pankaj singh

Reputation: 105

Use this:

CREATE TABLE APP(
    ID   INT NOT NULL,
    DT DATE ,
    ADDRESS  NVARCHAR (100) ,
    PRIMARY KEY (ID)
);

Upvotes: 1

Harshavardhan Kalsait
Harshavardhan Kalsait

Reputation: 1

Use this.

CREATE TABLE:

CREATE TABLE EMP
(EID NUMBER(20),
ENAME VARCHAR2(20),
DT DATE,
SAL NUMBER(20));

INSERT INTO THE TABLE:

INSERT INTO EMP (EID,ENAME,DT,SAL) VALUES(01,'ABCDE','11.NOV.2011',10000);

O/P OF ABOVE TABLE:

SELECT * FROM EMP;

EID ENAME DT SAL

01 ABCDE 11-DEC-11 10000

Upvotes: 0

Martin Brown
Martin Brown

Reputation: 25310

You don't need to specify the format in the table definition as dates are stored in a binary format.

CREATE TABLE APP(
    ID   INT NOT NULL,
    DT DATE,
    ADDRESS  NVARCHAR (100) ,
    PRIMARY KEY (ID)
);

When you try to insert into that table however, the server will try to convert the string to a date before inserting it. This can be problematic as it is unable to tell if 12.11.2017 is the 12th of November or 11th of December. To figure this out it uses the localization settings of the user account that is performing the operation.

Often you will find that the account that is running the operation is set to USA format, month day then year (MDY), when what you want is day month year (DMY) format. One way to tell it what the sequence of the date's parts is to use the DATEFORMAT setting like this:

SET DATEFORMAT dmy;

INSERT INTO APP (ID, DT)
VALUES (1,'22.12.2016')

Another alternative is to cast the string to a date using the CONVERT function and tell it what the date format is. The formats have numeric codes like 104 for German format Like this:

INSERT INTO APP (ID, DT)
VALUES (2,CONVERT(date,'22.12.2016',104))

Upvotes: 11

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Dates are stored in an internal format. Formats only make sense for input and output.

In your case you want the date in a German format (104), so you can use:

select convert(varchar(255), dt, 104)

If you like, you can include the formatted date as a separate column:

CREATE TABLE APP (
    ID INT NOT NULL,
    DT DATE,
    ADDRESS  NVARCHAR(100),
    DT_FORMATTED AS (convert(varchar(255), dt, 104)),
    PRIMARY KEY (ID)
);

You can then refer to dt_formatted to get the string in the format you want.

Upvotes: 5

Related Questions