CODEWITHSUNDEEP
sqlsql-serversqldatatypes
Mahdi Tahsildari
Mahdi Tahsildari

Reputation: 13582

Best way to compare Time (or Date) in sql?

I need two comparisons in my Sql server, One between Dates and One between Times. I'm using C# and Sql Server and Linq-to-sql. I want to store two time fields (Enter-Time, Exit-Time). Now I want to get the difference (e.g. 12:30 , 18:15 the difference is 5:45). What Data Type should I use? I have the same issue with Dates, I want to get the difference of two Dates but "DateTime" Data Type stores redundant data about time, I just need date. I want the easiest way with the least code possible. I'm currently saving like this "1045" when I fetch it I add a ":" to the middle and it becomes "10:45" and there are lot's of problems this way.

Upvotes: 1

Views: 659

Answers (2)

Simon
Simon

Reputation: 9425

If you need to store dates, you can use the date datatype. Similarly for times, you can use the time datatype.

To compute the difference, you can use the DATEDIFF() sql function that returns the difference between two dates: http://msdn.microsoft.com/en-us/library/ms189794.aspx

I would advise against storing your data as a string, int etc. Use the datatypes as they are intended.

Upvotes: 3

Ryan Amies
Ryan Amies

Reputation: 4922

For storing the data, you can use a Date type, or Time type. Once you do this the TimeSpan structure can be used (in your application) to measure differences in time/dates. If you subtract one date from another then you will receive a TimeSpan object back.

var difference = Date1.Subtract(Date2);

Upvotes: 1

Related Questions