Hi, I have a simple yet complicated question. Why time in SQL is limited from -838:59:59 to +838:59:59 and why does date start from 1970-01-01 13:05:00?
where did you get that information ?
From DATE and TIME datatype references. And i'm curious why it is so.
can you provide the link to that reference ?
No, can't find it now. But I've seen it in a couple of SQL books and professor at a class told us that and asked if anyone knew why. He also told that whoever finds the correct answer gets a highest mark on a midterm without writing a test.
for SQL Server, the range is as specified in this link
https://msdn.microsoft.com/en-sg/library/ms186724.aspx#DateandTimeDataTypes
Absolutely not the right information for SQL Server, specifically T-SQL. That's gotta be for some other database engine or someone with a Unix background got seriously confused with SQL Server.
The link that khtan provided is correct for SQL Server T-SQL.
maybe I've just came to the wrong place to ask my question)
Maybe. Maybe not. You simply haven't yet identified the SQL engine that you're working with and, from the sounds of it, neither has your professor. What database engine were you concentrating on in class?
In SQL Server, time is not limited to +/- 838:59:59. It's actually limited (as of 2008) to +/- 24 hours minus 40 microseconds. If using DATEPARTs, then it's limited to 2,147,483,647 date parts whether they be milliseconds or years with the caveat that there's another limit where you cannot exceed 9999-12-31 23:59:59.997 for some of the datatypes and 40 microseconds less than the year 10,000 for the rest.
And the date in SQL Server doesn't start at 1970-01-01 13:05:00. Depending on the datatype you use, it either starts at 0001-01-01 00:00:00.0000000 or 1753-01-01 00:00:00.000.
Like I said, we need to know what SQL engine you're using because "SQL is NOT SQL".
Since you brought up 1970-01-01, that sounds like some sort of Unix/Linux based system which is based on an epoch based on the birthdate of UNIX 1. You can find out a whole lot more about it using the following Google search.
In 2038, the unix epoch will wrap back to 0 and that's going to be a real problem that will make Y2K look silly.