smalldatetime

Question

Post reply

smalldatetime

Rahul The Dba

SSChasing Mays

Points: 647
More actions
December 28, 2010 at 10:03 pm

#229102

Comments posted to this topic are about the item smalldatetime
[font="Comic Sans MS"]Rahul:-P[/font]

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

Hardy21

SSCrazy Eights

Points: 9708

December 28, 2010 at 10:05 pm

#1267015

Nice question.

Thanks

paul.knibbs SSCoach Points: 15320 More actions · Answer 2

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.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

Good (if fairly easy) question, but bad explanation.

The default for smalldatetime is not Jan 1, 1900; it is NULL. Here is the proof:

DECLARE @d1 smalldatetime;

SELECT @d1;

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.

DECLARE @v0 float, @v1 char(20), @v2 varbinary(max), @v3 xml, @v4 uniqueidentifier;

-- Show default values - NULL for all data types

SELECT @v0, @v1, @v2, @v3, @v4;

-- Set to result of implicit conversion of 0 to specified data type

SET @v0 = 0;

SET @v1 = 0;

SET @v2 = 0;

-- Uncomment to get conversion errors

--SET @v3 = 0;

--SET @v4 = 0;

-- Show results

SELECT @v0, @v1, @v2, @v3, @v4;

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 4

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

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 5

Great question.

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

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

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 7

Whenever we use any data type we should know it's default value

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kiran_gowda81 SSChasing Mays Points: 645 More actions · Answer 9

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.

Regards

Kiran

UMG Developer SSChampion Points: 13482 More actions · Answer 10

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

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

Tom

Toreador SSChampion Points: 11365 More actions · Answer 12

"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 🙂

Reji PR SSC Veteran Points: 251 More actions · Answer 13

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 = 0

print Isnull(@date1,getdate())

Thanks.

Reji P R

Hyderabad

Thanks.

Reji PR,
Bangalore
😀

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 14

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

Thanks