DATETIME vs bigint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

DATETIME vs bigint

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-05-27 : 04:28:51

i want to storage datetime as :
DATETIME 8 bytes
Or
bigint 8 Bytes
both of 8 bytes
will there be diffrence in the Index size? in performance?

Thanks
Peleg

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 04:39:03

I always would store a datetime value as a datetime data type.
I don't see any reason to convert it to anything else.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-05-27 : 04:44:42

DATETIME is understood, by SQL, as being ... ermmmm ... a Date+Time. BigInt isn't

So with DATETIME you can use functions which understand dates - Years, Months, Days, Hours, Minutes, seconds etc.

You can get everything in the last year (without having to worry about leap years etc). Everything yesterday (from midnight-to-midnight). And so on ...

... and the ability to Format the value in any human-readable format you like.

Annoys me having to do CONVERT(varchar(24), DATEADD(Second, SomeDateColumnFromOracle, '19700101')) just to see what an integer "date-time" actually is.

Much more tedious using BigInt.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-27 : 05:02:05

Even if using BigInt uses less storage and gives better performance, with the current computing power and cheap disk space, the different might be just marginal. As Kristen as shown, the ease of coding and maintainability out weight that.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 05:08:54

If you want to store dates, always use proper datetime datatype
But you would be surprised that SQL Server internally store dates as two 4-byte integers
Becuase many people dont understand how datetime column works in SQL Server, I have started a blog series of Understanding Datetime column. If you are interested, follow it
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-27 : 07:52:29

It depends on your requirements.

If only seconds are stored, use DATETIME2(0) which uses only 6 bytes with the range of 0001-9999 for years.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources