What Is the Internal Representation of Datetime in SQL Server

What is the internal representation of datetime in sql server?

It's stored as an 8 byte field, capable of a range from 1753-01-01 through 9999-12-31, accurate to 0.00333 seconds.

The details are supposedly opaque, but most resources (1), (2) that I've found on the web state the following:

The first 4 bytes store the number of days since SQL Server's epoch (1st Jan 1900) and that the second 4 bytes stores the number of ticks after midnight, where a "tick" is 3.3 milliseconds.

The first four bytes are signed (can be positive or negative), which explains why dates earlier than the epoch can be represented.

Time part of a DateTime Field in SQL

In SQL Server if you need only the hh:mi, you can use:

DECLARE @datetime datetime

SELECT @datetime = GETDATE()

SELECT RIGHT('0'+CAST(DATEPART(hour, @datetime) as varchar(2)),2) + ':' +
       RIGHT('0'+CAST(DATEPART(minute, @datetime)as varchar(2)),2)

why is my 126 datetime not coming back with a T in the middle?

You are mixing up datetime values with formatted strings.

The first two examples doesn't return the date formatted in any way at all, it's just a datetime value. How the value is formatted into text is decided by how you display the value after getting it from the database. If you lived in a different country so that your default culture settings were different, the date could for example be displayed as 1/25/2010 10:14 AM instead.

In the second example the format parameter (126) is ignored, as there is no formatting or parsing involved when converting from a datetime value to a datetime value.

The third example formats the datetime value into a string before it's returned from the database, that's why you get it in the format that the database uses.

If you use a union with difference data types, it's the type precedence that decides the type of the result. The datetime type has higher precedence than nvarchar, so it attempts to convert the nvarchar values to datetime.

Why does the datetime type in SQL Server Compact round values?

I don't know the full internal details or the motivation behind the choice, but datetime is stored internally as - essentially - two 4-byte integers. One represents date, the other represents time. I suspect you lose some precision in the latter because of the way ticks / milliseconds have been handled since the very first versions of SQL Server, but again, I don't know low-level implementation details.

Related questions for more background info:

• What is the internal representation of datetime in sql server?

• Allow Entity Framework 4.5 to use datetime2 with SQL Server CE4

In order to support the precision you want without moving the value in and out of binary format, I would suggest using LocalDB which has the same portability advantages of Compact but without many of the feature limitations (such as support for the more precise datetime2 type - which I assure you takes 6-8 bytes, not 54 :-)).

SQL server DateTime and C# DateTime

The DateTime, which is represented by the string, isn't supported by the calender.

This error is being given because your C# application views the date 2012-14-10 as saying the 14th month, 10th day, and 2012th year. The day and year work find, but the month doesn't. Further, don't try and change how your C# application views the date, that's based off the culture of the system.

You're confusing how to define a DateTime object and how to display one.

Since you're storing your date as a DateTime in SQL, there's not a good reason for me to believe that you would need to do any kind of parsing. Consider the below code sample.

var dataTable = new DataTable();
var dataAdapter = new SqlDataAdapter("SELECT * FROM YourTable", "{connection string}");

dataAdapter.Fill(dataTable);

var yourDate = dataTable.Rows[0]["Date"]; <=== the type will be DateTime, simple.

Adding Parameters

Let's take your example query:

"SELECT * FROM date_test WHERE id = '4' AND date BETWEEN '" + dt1 + "' AND '" + dt2 + "'";

And let's fix it a bit, consider the below example:

var dataTable = new DataTable();
var dataAdapter = new SqlDataAdapter("SELECT * FROM date_test WHERE id = @ID AND date BETWEEN @StartDate AND @EndDate", "{connection string}");

dataAdapter.SelectCommand.Parameters.AddWithValue("@ID", "4");
dataAdapter.SelectCommand.Parameters.AddWithValue("@StartDate", new DateTime(2012, 10, 1));
dataAdapter.SelectCommand.Parameters.AddWithValue("@EndDate", new DateTime(2012, 10, 14));

dataAdapter.Fill(dataTable);

var yourDate = dataTable.Rows[0]["Date"]; <=== the type will be DateTime, simple.

Should I use the datetime or timestamp data type in MySQL?

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

---

Related Topics