XML FeedsSomeone tried to figure out why his data was showing the next day when he passed in today's date. If you are not careful to use the same data type and this includes scale and precision as well, you can get some strange results. In this post I will take a look at date, integer, varchar and decimal data types
Dates
When using dates make sure that you are using the same data type, don't mix datetime and smalldatetime. If you do, you can get some unexpected results, let's take a look
First create this table with a datetime column
- CREATE TABLE TestDatetime(SomeDate DATETIME)
- GO
Now create this proc which accepts a smalldatetime
- CREATE PROC prTestDatetime
- @SomeDate SMALLDATETIME
- AS
- INSERT TestDatetime VALUES(@SomeDate)
- GO
Now call the procedure with the following value
- DECLARE @d DATETIME
- SELECT @d = '2011-04-04 23:59:59.000'
- EXEC prTestDatetime @d
- GO
When you check the table now you will see that it has become the next day
- SELECT * FROM TestDatetime
2011-04-05 00:00:00.000
The query below will illustrate the same problem
- DECLARE @d DATETIME
- SELECT @d = '2011-04-04 23:59:59.000'
- SELECT CONVERT(DATETIME,@d), CONVERT(SMALLDATETIME,@d)
Output
-------------------------------------------
2011-04-04 23:59:59.000 2011-04-05 00:00:00
What happens is because smalldatetime is accurate to 1 minute, it rounds up to the next hour and thus it becomes the next day
Usually stuff like this happens where the table gets changed but someone forgot to also change the procedure, it could take a while until you catch a bug like this because unless you are passing in the last minute of the hour you won't see it...however the fact that the seconds are all 00 should give it away
Integer data type
When dealing with integers, you are in luck because it will just blow up in your face
Create this stored procedure
- CREATE PROC prTestInt
- @Someint smallint
- AS
- SELECT @Someint
- GO
Run it by passing in something that is greater than the small integer data type can hold
- DECLARE @i int
- SELECT @i = 99999
- EXEC prTestInt @i
- GO
And here is the error.
Msg 8114, Level 16, State 5, Procedure prTestInt, Line 0
Error converting data type int to smallint.
This is a good thing, you will be able to catch this immediately. At least it doesn't do a negative overflow like in some languages
varchar, nvarchar, char and nchar
varchar, nvarchar, char and nchar have a bunch of interesting inconsistencies, this can really bite you if you are not careful
Here is one example, create the following procedure
- CREATE PROC prTestVarchar
- @Somevarchar varchar(3)
- AS
- SELECT @Somevarchar
- GO
Now run it like this
- DECLARE @v VARCHAR(10)
- SELECT @v = '9999999999'
- EXEC prTestVarchar @v
- GO
Output
---------
999
Since you specified varchar(3), SQL Server trims everything over 3 bytes
What if you just use varchar?
People coming from languages where you define something as a string usually make this mistake. Take a look at this: Issue inserting text into table from c# proc parameter
Create the following stored procedure
- CREATE PROC prTestVarchar2
- @Somevarchar varchar
- AS
- SELECT @Somevarchar
- GO
Run the proc
- DECLARE @v VARCHAR(10)
- SELECT @v = '9999999999'
- EXEC prTestVarchar2 @v
- GO
Output
--------
9
In this case SQL Server used a size of 1 since nothing was specified. However when you use varchar in a cast or convert function and you don't specify a size, it will default to 30 characters
- SELECT CONVERT(VARCHAR,'1111111111222222222233333333334')
111111111122222222223333333333
As you can see, the last character is not displayed
Take also a look at this post Always include size when using varchar, nvarchar, char and nchar by George Mastros and this post Bad habits to kick : declaring VARCHAR without (length) by Aaron Bertrand for some more info
Decimal/Numeric
Decimal (or numeric) will round down or up if it can't hold the whole value
Take a look by running this
- DECLARE @d DECIMAL(4,3)
- DECLARE @d2 DECIMAL(4,2)
- SELECT @d = 1.999
- SELECT @d2 = @d
- SELECT @d,@d2
Output
-------------
1.999 2.00
As you can see 1.999 will round up to 2.00 if your scale is less than the number of digits passed in
If you have to do multiplication you have to be extra careful and have enough space to avoid rounding issues, I deal with this all the time because we have to show 10 digits for scale.
Decimal and numeric will default to (18,0) if you don't specify anything when declaring them, see this post Decimal and Numeric problems when you don't specify precision and scale by George Mastros for more info, no need for me to repeat the same.
Conclusion
Make sure that your data types or data type sizes are the same for variables/parameters and tables, if they are not, you might not notice the problem right away and it can be a real pain in the neck to make the change down the road
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Very informative Denis!
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.