How to format Datetime and datetime2 in C# and SQL to fit my needs

Question

I'm quite new to this, I'm using MVC view model that has a property with Datetime type :

namespace FP.WebUI.Models
{
    public class AdminSessionsViewModel
    {
        NewSession _NewSession = new NewSession();
        public NewSession NewSession { get { return _NewSession; } set { _NewSession = value; } }
        public IEnumerable<Sessions> CurrentSessions { get; set; }
    }

    public class NewSession
    {
        [Required]
        public string Title { get; set; }
        public string Description { get; set; }
        [Required]
        public DateTime Date { get; set; }
    }
}

I want Date property to be like this format : 27/05/2013 06:44AM and the same inside SQL database.

I really don't know how to configure Date inside the NewSession class to automatically get shown like this inside my Textbox and when mapped back to Entity Framework will be saved inside the database like this.

Here's my entity framework fluent API configuration :

namespace FP.Domain.Configurations
{
    public class SessionsConfig : EntityTypeConfiguration<Sessions>
    {
        public SessionsConfig()
        {
            ToTable("Sessions");
            Property(p => p.Name).IsRequired();
            Property(p => p.PhotosCount).IsRequired();
            Property(p => p.CoverPhoto).IsRequired();
            Property(p => p.Date).HasColumnType("datetime2").HasPrecision(0);
        }
    }
}

Why do you want the value to be a string in your database at all? If it's *not* a string, then the format you happen to see it in when you query the database is irrelevant. — Jon Skeet, Apr 03 '13 at 16:56
I'm putting the value in a `readonly` Textbox with `datepicker` jquery plugin attached to it. — Rafael Adel, Apr 03 '13 at 17:00
So do the formatting at the C# side - fine. Given that you're using an ORM, you shouldn't need to worry about the database side at all. The very notion of what "format" it's in in the database is a red herring - it's like worrying about whether a number in the database is represented as 0x10 or 16. — Jon Skeet, Apr 03 '13 at 17:01

score 2 · Accepted Answer · edited May 23 '17 at 12:16

2

You need to add [DisplayFormat] attribute to your DateTime property. That should take care of the user interface. See this answer: Converting DateTime format using razor

[Required]
[DisplayFormat(DataFormatString = "{0:dd/MM/yyyy hh:mm tt}", ApplyFormatInEditMode = true)]
public DateTime Date { get; set; }

You don't need to control how the date time is stored in SQL server - it will be stored as date, not as a string. You can use FORMAT() function in T/SQL to format that date in stored procedures etc. If you don't have MSSQL 2012, you can use DATEPART() or CONVERT() functions.

edited May 23 '17 at 12:16

Community

1
1

answered Apr 03 '13 at 16:53

Knaģis

20,827
7
66
80

I've added these annotations, but sometimes when i select a date using jquery datepicker i get ` The field Date must be a date. ` validation error message. – Rafael Adel Apr 03 '13 at 19:14

How to format Datetime and datetime2 in C# and SQL to fit my needs

1 Answers1