SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Rahul Kr. Ghosh / smalldatetime / Latest Posts

RE: smalldatetime

john.arnott — Wed, 05 Jan 2011 19:59:10 GMT

I cannot recall ever using smalldatetime, and now this QOD gives me good reason to avoid it. A max value in year 2079 implies that anyone attempting to save a few bytes per row with this datatype may be leaving a Y2K-like mess for a future programmer or DBA to clean up.

RE: smalldatetime

Hardy21 — Tue, 04 Jan 2011 22:00:54 GMT

@rejipr1982: Hugo already mentioned this in his response. Still thanks to evaluate the same.

RE: smalldatetime

Reji PR — Tue, 04 Jan 2011 11:21:12 GMT

Thanks for the question.The default value for the @date1 is NULL.Please see below proof. If I am commenting the 'set statement, I am getting @date1 as NULL.declare @date1 smalldatetime--set @date1 = 0print Isnull(@date1,getdate())Thanks.Reji P RHyderabad

RE: smalldatetime

Toreador — Tue, 04 Jan 2011 03:15:56 GMT

"Whenever we use any data type we should know it's default value"I disagree (even ignoring the valid but slightly pedantic points about datatypes not having defaults).We should never rely on default values in this way, it is prone to error, and hard to read the code.If you want to assign a value to a date variable, then explicitly assign the value you want, don't give it a value of a different datatype and rely on implict conversion :-)

RE: smalldatetime

L' Eomot Inversé — Mon, 03 Jan 2011 10:41:11 GMT

Nice question, terrible explanation. SQL doesn't even have a concept of default values for its various datatypes.

RE: smalldatetime

UMG Developer — Thu, 30 Dec 2010 11:55:46 GMT

Thanks for the question, and thanks Hugo for your explanation.This question was really easy, as I knew the errors couldn't happen as they were stated, so there was only one option left.

RE: smalldatetime

kiran_gowda81 — Wed, 29 Dec 2010 11:04:21 GMT

When a variable is declared in SQL by default it's value will be NULL, there is no default value concept in SQL variable declaration.RegardsKiran

RE: smalldatetime

SQLRNNR — Wed, 29 Dec 2010 09:39:39 GMT

Thanks for the question and thanks to Hugo for the explanation.

RE: smalldatetime

Daniel Bowlin — Wed, 29 Dec 2010 07:34:50 GMT

[quote]Whenever we use any data type we should know it's default value[/quote]Although I will agree that knowing these things has value, I am more of the opinion of never leave anything to default, declare everything. This way the default becomes irrelevant and you are much less likely to get a surprise in your results.

RE: smalldatetime

Koen Verbeeck — Wed, 29 Dec 2010 07:31:27 GMT

Nice, easy question. And thanks to Hugo for the extra, thorough explanation.

RE: smalldatetime

Scott Arendt — Wed, 29 Dec 2010 07:11:15 GMT

Great question.I didn't know it beforehand, but it sure made sense when I thought the question over.

RE: smalldatetime

ronmoses — Wed, 29 Dec 2010 05:40:21 GMT

Well that's what I get for second-guessing myself. It seemed like such an easy question, the obvious answer couldn't possibly have been right. Guess that'll teach me! ;-)Ron

RE: smalldatetime

Hugo Kornelis — Wed, 29 Dec 2010 05:08:34 GMT

Good (if fairly easy) question, but bad explanation.The default for smalldatetime is not Jan 1, 1900; it is NULL. Here is the proof:[code="sql"]DECLARE @d1 smalldatetime;SELECT @d1;[/code]Also, setting a variable to 0 is not at all requesting to set it to the default, it is requesting to set ot to the value 0, or whatever is the result of implicitly converting 0 to the data type of the variable.[code="sql"]DECLARE @v0 float, @v1 char(20), @v2 varbinary(max), @v3 xml, @v4 uniqueidentifier;-- Show default values - NULL for all data typesSELECT @v0, @v1, @v2, @v3, @v4;-- Set to result of implicit conversion of 0 to specified data typeSET @v0 = 0;SET @v1 = 0;SET @v2 = 0;-- Uncomment to get conversion errors--SET @v3 = 0;--SET @v4 = 0;-- Show resultsSELECT @v0, @v1, @v2, @v3, @v4;[/code]

RE: smalldatetime

paul.knibbs — Wed, 29 Dec 2010 02:17:06 GMT

Not so sure about the explanation given--smalldatetime doesn't have a "default value" of 1 Jan 1900, it's just that the underlying number that stores the date uses 0 to represent that date.

RE: smalldatetime

Hardy21 — Tue, 28 Dec 2010 22:05:44 GMT

Nice question.

smalldatetime

Rahul The Dba — Tue, 28 Dec 2010 22:03:22 GMT

Comments posted to this topic are about the item [B]smalldatetime[/B]