Datetime Default

Question

Post reply

Datetime Default

malleswarareddy_m

SSCertifiable

Points: 5847
More actions
November 26, 2012 at 9:22 pm

#285098

Comments posted to this topic are about the item Datetime Default
Malleswarareddy
I.T.Analyst
MCITP(70-451)
November 27, 2012 at 12:16 am

This was removed by the editor as SPAM

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

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Good question indeed!

The result would be same if, I replace the second insert statement with 0 (zero) from space.

insert into #test

select 1,'malli',null

union

select 2,'reddy',0

union

select 3,'test',getdate()

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Ken SSCoach Points: 17191 More actions · Answer 2

Nice question. However, you can actually run the code without the default on the dateofjoin column and get the exact same result for id 2. The blank space is causing the datetime column to default to 1/1/1900 instead of the default constraint on the column being applied. As Lokesh mentioned, a zero would cause the same behavior.

create table #test(id int,EmpName varchar(50),dateofjoin datetime)

insert into #test

select 1,'malli',null

union

select 2,'reddy',' '

union

select 3,'test',getdate()

select * from #test

drop table #test

/*--Results

id EmpName dateofjoin

----------- ------------ -----------------------

1 malli NULL

2 reddy 1900-01-01 00:00:00.000

3 test 2012-11-27 00:09:06.313

*/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Nice question. The explanation could be a bit more clear.

It's not the default from the default expression GETDATE(), but the default value that is chosen when 0 or space is converted to a datetime.

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 4

Actually the "default value" in the link mainly tells on the default value of the DATETIME datatype and the date value is not beyond that.

The usage of the GetDate() as the DEFAULT value (which is has a constraint) it always picks the today's date and time and it never pick 1900 one.

Because, (like the other said) "single space" or any non string in the non-date format will always returns the default value (and not because the DEFAULT constraint, but because of the datetime datatype is the way it is)

try the below code, it takes exactly the today's date and it inserts.

insert into #test (id, empname) values (4, 'qtod')

When a value is going to be passed to the column which has DEFAULT value constraint

1. either you have to pass perfect value of the date which can be the date other than 1900 one

OR

2. do not pass the value at all to that column, and let the constraint take care of.

passing single space and zero (not sure, and which I have not used anywhere in real projects) does not adds the meaning to it.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 5

Easy question, but answer really wrong!

The DEFAULT is used only if no value is specified for the column or DEFAULT VALUES keyword.

-- default is triggered

create table #b (a datetime default getdate())

insert #b default values

or

insert #b (a) values(default)

-- default is NOT triggered, because a value is specified

But, insert #b select ''

Toreador SSChampion Points: 11368 More actions · Answer 6

Not sure how this one got through quality control, as the explanation is entirely wrong.

The value of '1900-01-01' does not arise because of any default - the default will not come into play becauise a value has been specified, and in any case the default is getdate().

The value arises because the specified value ' ' is implicitly cast to datetime, and casting an empty string to a date gives that value.

select cast(' ' as datetime)

will give '1900-01-01 00:00:00.000'

paul s-306273 SSChampion Points: 10730 More actions · Answer 7

paul s-306273

SSChampion

Points: 10730

November 27, 2012 at 2:47 am

#1562563

Easy question and I got it wrong.

Aargh...

kalyani.k478 Default port Points: 1400 More actions · Answer 8

kalyani.k478

Default port

Points: 1400

November 27, 2012 at 2:58 am

#1562568

easy question:)

seankyleprice SSC Veteran Points: 214 More actions · Answer 9

Given that the nullability of the column wasn't explicitly specified (a personal bugbear of mine) surely the answer depends on the setting for 'ANSI null default' on tempdb and whether or not ANSI_NULL_DFLT_ON is set for the connection?

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

Koen Verbeeck (11/26/2012)
Nice question. The explanation could be a bit more clear.

That is a very polite way to put it.

I'll be more clear: the explanation is rubbish! The default is only used if no value is given in the INSERT statement, and in this question a value was given in all cases - either NULL, or a blank space (which gets converted to 1/1/1900), or the current date and time. In cases where the default is used, the stated default is actually used. If the text in the explanation were actually true, I would be submitting a bug report right now!

(Unfortunately, I lost my point due to stupidity - I knew the correct answer, then clicked the wrong option. :crying:

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/

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 11

Thanks for the interesting question. I learned about datetime defaults through the kind and unkind comments.

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Hugo Kornelis (11/27/2012)
Koen Verbeeck (11/26/2012)
Nice question. The explanation could be a bit more clear.
That is a very polite way to put it.
I'll be more clear: the explanation is rubbish! The default is only used if no value is given in the INSERT statement, and in this question a value was given in all cases - either NULL, or a blank space (which gets converted to 1/1/1900), or the current date and time. In cases where the default is used, the stated default is actually used. If the text in the explanation were actually true, I would be submitting a bug report right now!

I agree almost totaly. My sole reservation is that you omitted the word "utter" before "rubbish".

Apart from the explanation, it's a nice question about the default not being used when a value is supplied; rather a basic question, of course - the purpose of defaults is to cqater for the case where no value is supplied, and that is very basic.

Tom

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 13

Hugo Kornelis (11/27/2012)
Koen Verbeeck (11/26/2012)
Nice question. The explanation could be a bit more clear.
That is a very polite way to put it.

That's the difference between the Belgians and the Dutch 😉

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