SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Datetime Default


Datetime Default

Author
Message
malleswarareddy_m
malleswarareddy_m
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3413 Visits: 1189
Comments posted to this topic are about the item Datetime Default

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Lokesh Vij
Lokesh Vij
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4792 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Ken Wymore
Ken Wymore
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8311 Visits: 2426
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
Koen Verbeeck
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111844 Visits: 13338
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Raghavendra Mudugal
Raghavendra Mudugal
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4626 Visits: 2958
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.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27497 Visits: 7549
Koen Verbeeck (11/26/2012)
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.

+1

Bear in mind that for DATETIME data types, the Date part is stored as the number of days since "1900-01-01".
Therefore, when a 0 (or space , which is "converted" to 0) is inserted, it will equate to said "1900-01-01".

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Carlo Romagnano
Carlo Romagnano
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10045 Visits: 3466
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
Toreador
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5012 Visits: 8188
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
paul s-306273
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4693 Visits: 1224
Easy question and I got it wrong.

Aargh...
kalyani.k478
kalyani.k478
SSC Eights!
SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)

Group: General Forum Members
Points: 876 Visits: 97
easy questionSmile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search