CODEWITHSUNDEEP
c#entity-frameworkdatetimesql-server-2014
Alex
Alex

Reputation: 155

Why the DATETIME value that I send from C# is stored in SQL Server with a difference of one day/hours?

you probably will tell me that there's another similar question like mine out there. The only thread I found that was kinda the same was this:

DateTime values read wrongly from Database

The same thing is happening to me.

Our company manages a web application all over the state. We are in the "head quarter", where multiple application servers connects to one DB Server, that's running SQL SERVER 2014.

The users have to save the birthdate of students, e.g, 2008-10-03. No hour is set, so, it's supposed to save 00:00:00.000. The application is written in C#, using EntityFramework for the DB Connection.

Not all of the records are saved wrongly, but others are saved like this: 2008-10-02 22:00:00.000. Two hours less, and, because of that, it's not the same day anymore.

The birthdate is a DateTime field. How can I make this thing work correctly? Should I use a datetimeoffset?

I appreciate your time and I really hope you can help me. Thanks!

Edit

The code where this happens, would be like this:

public saveData (DateTime birthdate, int studentId){
    using(Entities DB = new Entities()){
        student myStudent = findStudent(studentId);
        myStudent.birthDate = birthdate;
        DB.SaveChanges();            
    }
}

As I said, I'm using entityframework.

We have a network department, and they've granted us multiple virtual application servers, so I couldn't tell you what's the region of the machine, because we're not identifying which server is saving the information (maybe if we do so, that would help us).

It's a little embarrassing, but I don't have a lot of experience with DateTime considering all these aspects. How can I make sure that my datetimes are considered UTC/Zulu?

Thanks for the quick responses

Upvotes: 2

Views: 1540

Answers (1)

Jasen
Jasen

Reputation: 14250

Specify UTC when creating the DateTime so that all calculations are normalized no matter where (time zone) the machines might reside.

DateTime(year: 2008, month: 10, day: 3,
  hour: 0, minute: 0, second: 0,
  kind: DateTimeKind.Utc)

However, since this date probably originates from a user interface you are likely working with strings so you can parse the date with a DateTimeStyles flag.

With time 00:00:00Z to force the UTC date

var utc = DateTime.Parse("2008-10-03T00:00:00Z", null,
    System.Globalization.DateTimeStyles.AdjustToUniversal)

Without the time works as well

var utc = DateTime.Parse("2008-10-03", null,
    System.Globalization.DateTimeStyles.AdjustToUniversal)

Now persist the UTC DateTime

saveData(utc, studentId)

Upvotes: 2

Related Questions