which one is better? whether to store date as date or int?

we are storing date into either date datatype or int datatype which one is better and why?

more ▼

asked May 31 '10 at 09:15 AM in Default

Dharmendra gravatar image

44 7 7 7

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

DATE supports date functions like DATEADD and DATEDIFF which INT does not. DATE also automatically includes validation because only valid dates are allowed. With INT you'd presumably have to add validation to validate the date values. Certainly use DATE unless you have some very good reason not to.

As a matter of detail, DATE is smaller than INT (3 bytes instead of 4).

more ▼

answered May 31 '10 at 09:50 AM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

i think int datatype is batter because it occupy less space then date datatype. and we can convert it to date or datetime whenever neccessory. is it true?

more ▼

answered May 31 '10 at 09:26 AM

Dharmendra gravatar image

44 7 7 7

Surely, if INT was better then there would be no need for TIME/DATE/DATETIME types and they would not exist in SQL Server?
May 31 '10 at 12:10 PM Fatherjack ♦♦
yes i got it, thanks man. my sir says that always store date as int so i would like to ask you why he says me like this? is there any reason behind it?
Jun 01 '10 at 01:43 AM Dharmendra
I think that's a major error. Store the data in the data type that best reflects it. After all, we could store everything in a char field, but we don't. Numbers go in numbers fields, strings in string fields, and dates in date fields.
Jun 01 '10 at 08:48 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 31 '10 at 09:15 AM

Seen: 1432 times

Last Updated: May 31 '10 at 03:09 PM