Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Question 1

I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example

20120101  -- 01 Jan 2012

Is there any advantage of keeping numeric date system and not using SQL Datetime?

Question 2

Now I am trying to loop through numeric date to find customers between two dates. If the start and enddate encompass two months, I get thousands of records instead of just 60. Example:

create table #temp1(day int,capacity int) /* just a temp table */

declare @start int 
declare @end int

set @start=20111201
set @end = 20120131

while (@start <= @end) 
Begin
    insert into #temp1  /* I am storing things in #temp table so data looks pretty */
    exec usp_GetDailyCap @date1= @start

    set @start = @start + 1;    
end

select * from #temp1

This pulls 8931 records instead of 60. Is there a better way to improve the logic above so I pull only valid dates? I tried IsDate and sub-queries but that did not quite work in an efficient way.

share|improve this question
    
If you're running SQL Server 2008 or higher, you can actually just use the data type Date. It's a bit smaller and doesn't force you to include time, but almost all of SQL's datetime functions still work for it. –  DForck42 Mar 27 '12 at 15:24
1  
I only see disadvantages in this approach no advantage whatsoever –  a_horse_with_no_name Mar 27 '12 at 17:24

3 Answers 3

To answer your first question, I would recommend using the DATETIME data type within SQL Server. Not necessarily for performance reasons, but to leverage the RDBMS-specific functionality. For instance, you would have to re-invent a lot of logic just to do basic date math (think DATEDIFF(), DATEADD(), DATEPART() and many other functions. They are obviously tailored to the DATETIME data type and are easy to work with).

As for your second question, you are running into the exact problem that the first question (and my answer) is geared towards. You are looking at 20111201 and 20120131 as dates, and your brain is telling you that should be a difference of 60 days. Well, you're looping through based off of the delta...which is:

20120131 - 20111201 = 8930 (with the inclusive loop it'll be 8931)

In other words, your WHILE loop is executing 8931 times. This is happening because those are integer values and your loop will not jump from 20111231 straight to 20120101.

You integers aren't going to take into account the cap of years and months (i.e. your Question 2 problem).

share|improve this answer
    
Well that is exactly my question. For numeric dates, loops can go into thousands, not just 30 days or 29 days. But bear in mind that I am working with a professional system. And even cisco uses it as it seems. –  Jackofall Mar 27 '12 at 13:56
3  
Besides performance and functionality, there's also integrity. With integers as dates, the db would allow 20121301 and 20120230 and even 20129999 as a date. –  ypercube Mar 27 '12 at 13:58
    
@Jackofall Cisco doesn't have the platform of an RDBMS behind it. They wrote their own logic. Why wouldn't they just use integers. From the ground up, that is probably the easiest way for low-level software. But we're talking about apples and oranges here. –  Thomas Stringer Mar 27 '12 at 13:59
3  
@Jackofall: There is a great difference between storing dates as integers (and having gaps) and storing datetimes/timestamps as integers - or even dates as integers, like VB/Excel does. –  ypercube Mar 27 '12 at 14:15
4  
There are many (if not most) professionally designed databases that use bad techniques. I have worked with many COTS products and not seen any that were well-deisgned from a database perspective. –  HLGEM Mar 27 '12 at 16:42
  1. Ralph Kimball recommends storing dates as integers. He has written a lot, both online articles and books.
  2. You can use a calendar table and issue consecutive numbers to your dates, as follows:

    Date Number

    20120229 1234

    20120301 1235

The calendar table has to be generated, but it is a very easy task.

share|improve this answer
    
@2, would this be a lookup table that I have to generate? –  Jackofall Mar 27 '12 at 15:14
1  
I'd like to see the case where you filter a query by joining to a date table with the dates stored as numeric and filtering those numeric dates would beat using "where [date] between @startdate and @enddate" –  DForck42 Mar 27 '12 at 15:36
1  
@DForck42 there is no need for the case you are suggesting: "where [dateAsInt] between 20120229 and 20120329" would return exactly the same rows as "where [date] between '20120229' and '20120329'" –  A-K Mar 27 '12 at 15:46
1  
And what was his reasoning? –  HLGEM Mar 27 '12 at 16:39

Potential data types and their sizes/limitations:

  • Decimal(8,0): 5 bytes
  • Date: 3 bytes, 0001-01-01 through 9999-12-31
  • Int: 4 bytes

Pros for numeric data type:

  • They look pretty?

Cons for numeric data type:

  • Requires custom code for handling date operations
  • Requires custom code to manage correct dates (ie, not allowing 20120230 [Feb 30th, 2012])
  • Larger data footprint when compared to the Date data type.

Honestly, you're better off using the date data type IMHO.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.