Time and Date

This is the start page where you can find code regarding time and date.

There are some time and date calculations available in SQL Server. However you need some basic knowledge to carry out them all. You also need to understand what time is.

So what is time? Time is basically a duration; a second is a huge number of oscillations of an atom. A year is the time it takes for our earth to travel a full circle around the sun. This is the big picture.

Most questions I get on the forums about time is about time conversion. The good thing is that all fundamental items have the same length. A second is a second no matter which calendar you use. That’s how you can convert an ORACLE value of 1211205037430 to the correct SQL Server equivalent.

But you can’t do that with the value itself! To solve the equation you will need to know the starting point (date zero) for both systems. In Oracle it’s usually January 1st 1970, while it’s January 1st 1900 in SQL Server.

The second thing you will need to know is the resolution of the value. Is it the number of second passed since date zero? No, not in this case. Is it number of milliseconds passed since date zero? It probably is. Adding 1211205037430 millseconds to January 1st 1970 returns 2008-05-19 13:50:37.430 so it sounds plausible. If you don’t know the resulution for the value you will have to know the result beforehand and you can calculate the resolution.



Leave a Reply