SmallDateTime vs DateTime in SQL Server

I just discovered this after pulling my hair out.... there are date max limitations in SmallDateTime.

SmallDateTime

January 1, 1900, through June 6, 2079
00:00:00 through 23:59:59

Date Time
January 1, 1753, through December 31, 9999

00:00:00 through 23:59:59.997

References:

http://msdn.microsoft.com/en-us/library/ms187819.aspx for DATETIME

http://msdn.microsoft.com/en-us/library/ms182418.aspx for SMALLDATETIME

Who Said You Cant Do A Loop in SQL

You can sort of do loops in SQL. Its not as nice as in regular programming but you do have a WHILE loop.

All you have to do is this:

WHILE whileCondition
BEGIN
What to do in the loop
END

I had to do some parsing of a string and I had to cycle through each letter to find a particular subset of characters.

There is also FETCH/NEXT but this is much easier

MAKE SURE THAT YOU WILL HIT YOUR WHILE CONDITION OR YOU WILL BE IN AN INFINTE LOOP :)

SQL: Dividing 2 Numbers Keeps Returning Zero (0)

This is way too funny....

So I am writing a SQL Stored Procedure.... hum de dum de dum......

Run it.....

WHAT!!!! The value of X/Y is 0 (put in Column Z)

X is 5000,
y is 10000

Both are decimal data types and the third field Z is also a decimal

Shouldnt that be .50

Well to make a long long long story :) short.... the 5,000 and 10,000 values were a sum in a previous step...

What I found out after a little blog research is that when you do a math function like SUM or COUNT, SQL changes the data type to INTEGER. That is why it was returning 0

Basically here is what I did

CAST(X as decimal(18,2)) / Cast(Y as decimal(18,2))

and it worked like a charm....

Thanks to this blog that saved me:

http://blog.pengoworks.com/index.cfm/2008/11/4/Division-of-integers-in-MS-SQL