CODEWITHSUNDEEP
c#asp.net-mvcdatetime
Trinity
Trinity

Reputation: 484

How to save date different format to SQL Server

I'm trying to save date in yyyy-mm-dd format instead of yyyy-dd-mm format to my database. I added

[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]

to the model class, but result didn't change. Date is still yyyy-dd-mm format in database.

Is it necessary doing it with C# code I can't change anything in database table. How can I do that ?

Upvotes: 0

Views: 1568

Answers (2)

Coskun Ozogul
Coskun Ozogul

Reputation: 2469

If your column is a string column, you should change it and use a date column as MarcE mentionned. But, according what I understood, you have a Date column and you want to change the format.

In sql server date is not store like a string. And the format that you see is associated the collation of your database.

If you want to change it, you should try to change date format of your db.

    -- Set date format to day/month/year.  
    SET DATEFORMAT dmy;  
    GO  
    DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';  
    SELECT @datevar;  
    GO  
    -- Result: 2008-12-31 09:01:01.123  
    SET DATEFORMAT dmy;  
    GO  
    DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';  
    SELECT @datevar;  
    GO  
    -- Result: Msg 241: Conversion failed when converting date and/or time -- from 
    character string.  

    GO

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql?view=sql-server-ver15

Upvotes: 0

MarcE
MarcE

Reputation: 3731

Dates in a (relational) database should be stored in a datetime column, or the equivalent for whatever flavour of database you are using (SQL server documentation). You can choose any format to display the date in your application, that has no bearing on how is stored.

If you're storing the dates in a database in a text format, you are storing up a world of pain that will come back to hurt you later on.

Upvotes: 6

Related Questions