When storing a datetime in sql server (datetime type), what format does it store it in?
When storing a datetime value in sql server 2008, using the datetime type, what format will it store the value in?
Is it the number of seconds since 1970 (or whatever)?
On the application side (.net), I am passing in a datetime object.
When I view the table’s rows in query analyzer, will it format it according to my culture settings or am I viewing exactly what is stored in the db?
4 Solutions collect form web for “When storing a datetime in sql server (datetime type), what format does it store it in?”
Internally, according to SQLDenis, they are stored as two integers
When you view them in the query analyzer you are seeing evaluated numbers.
It is not stored as “unix time”
It’s stored as a floating point representing days since January 1st, 1900. If you round it down by casting it to an int, that leaves only the date part:
select cast(cast(getdate() as int) as datetime) --> 2011-07-12 00:00:00.000
Or if you add a number to it, that’s adding a number of days. For example, adding 0.5 is equivalent of adding 12 hours:
select getdate() + 0.5 --> 2011-07-12 11:22:09.927
See this MSDN article for details.
Datetime does not include format information; query analyzer can display it in any way it likes. It will probably try to honor your client PC’s regional settings.
Here is the code to view how it is stored:
DECLARE @now datetime, @bytenow binary(8) SELECT @now = getdate() SELECT @bytenow = convert(binary(8), @now) SELECT @now AS fulldate, @bytenow AS internal, convert(int, substring(@bytenow, 1, 4)) AS days, convert(int, substring(@bytenow, 5, 8)) AS ticks
This shows the 2 values:
- the first 4 bytes is a number representing days since 1-1-1900
- the second 4 bytes is the number of ticks since midnight where one second has 300 ticks.
fulldate internal days ticks ----------------------- ------------------ ----------- ----------- 2016-11-07 10:54:13.080 0x0000A6B700B3AFB4 42679 11775924
Reference: Solving the Datetime Mystery see figure 3
DateTime doj = new DateTime(); System.Globalization.CultureInfo c1=new System.Globalization.CultureInfo("en-GB",true); doj = DateTime.Parse(Txtdoj.Text.Trim(), c1, System.Globalization.DateTimeStyles.NoCurrentDateDefault);
use query to display the date time
select convert (varchar(max),doj,103)as doj from tbl_registration