InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 00:14:46 GMT20http://www.sqlservercentral.com/Forums/Topic606618-145-1.aspx[quote][b]Madhivanan (11/27/2008)[/b][hr][url]http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/02/empty-string-and-default-values.aspx[/url][/quote]My point exactly and that is why I used the case for when I don't want to use the default. I will do some further testing with ASP.NET when you pass the empty string to the stored procedure from the UI and post the results here.:cool::cool:Thu, 27 Nov 2008 08:35:33 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic606618-145-1.aspx[url]http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/02/empty-string-and-default-values.aspx[/url]Thu, 27 Nov 2008 04:48:48 GMTMadhivanan-208264http://www.sqlservercentral.com/Forums/Topic606618-145-1.aspx[quote][b]Ninja's_RGR'us (11/26/2008)[/b][hr]Since when can DateofBirth = "" ???A date value is set to a date.... or null, period (sql server side).[/quote]Don't bite my head of now! I meant [code]case when DateofBirth = '' then NULL else DateofBirth end[/code]. That was a typo and yes, you can set a date as '' because then you will just get the default. I normally test before I post here. By the way when you are programming in e. g. Visual Basic the you do use "".Thanks for pointing out the typo.:hehe::hehe::hehe::hehe:Thu, 27 Nov 2008 04:20:51 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic606618-145-1.aspxSince when can DateofBirth = "" ???A date value is set to a date.... or null, period (sql server side).Wed, 26 Nov 2008 01:30:06 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic606618-145-1.aspx[quote][b]lrosales (11/21/2008)[/b][hr]Guys!I solved the problem. the answer is simply "Default"insert into tt99(Datetime2, description) values (default,'Testing the Default')simple is it not?:P[/quote]If you want to use a default date in that column then yes, it works well but if you do not want to insert anything if there is no date then better insert a NULL into the column like this:[code]insert into tt99(datetime2, description) values (NULL, 'Testing the Default')[/code]Let's say for instance you have a field DateofBirth and it is not compulsory to enter anything then you would not want to enter today's date but rather a NULL.Should this date be entered from a UI and the program returns an error when passing a NULL field then I would rather pass "" and in the SP put a case on the insert like this.[code]case when DateofBirth = "" then NULL else DateofBirth end[/code]That's just my bit of advice.Wed, 26 Nov 2008 00:08:57 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic606618-145-1.aspxWell ya, the gotcha here is that '' is a valid "DATE"... and I say that loosely, which equates to day 0 or '1900-01-01'.Since you do pass a valid "date" value in the insert, then it is used. There's no magic there.Hope this helps a few more members down the line :P.Now the real trick, is how do you choose wether to insert the default in your string or a passed value to the stored procedure in that insert statement... can't wait to see how you'll deal with that.Mon, 24 Nov 2008 05:36:25 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic606618-145-1.aspxGuys!I solved the problem. the answer is simply "Default"insert into tt99(Datetime2, description) values (default,'Testing the Default')simple is it not?:PFri, 21 Nov 2008 11:12:47 GMTlrosaleshttp://www.sqlservercentral.com/Forums/Topic606618-145-1.aspxDid You test it?Yes I did tried it.thanksFri, 21 Nov 2008 09:44:23 GMTlrosaleshttp://www.sqlservercentral.com/Forums/Topic606618-145-1.aspxIn order to use the default you cannot supply a value to the column. It is converting the '' to the 1900 date and not using the default.Try this.create table tt99(Datetime2 smalldatetime default Getdate() , Description nvarchar(200))insert into tt99(Datetime2, description) values ('12/20/08','Testing the Default')goinsert into tt99( description) values ('Testing the Default')select * from tt99drop table tt99Fri, 21 Nov 2008 09:40:15 GMTKenSimmonshttp://www.sqlservercentral.com/Forums/Topic606618-145-1.aspxguysthis may be something ya'll have probably encountered, but this is the first time I use the Date as the Default. here is my issue defaultiing to 1900-01-01. any suggestions? use personalgocreate table tt99(Datetime2 smalldatetime default (Getdate()) , Description nvarchar(200))insert into tt99(Datetime2, description) values ('12/20/08','Testing the Default')goinsert into tt99(Datetime2, description) values ('','Testing the Default')select * from tt99drop table tt99here are the results:2008-12-20 00:00:00 Testing the Default1900-01-01 00:00:00 Testing the Defaultthe last Record is the Problem.Thanks for any suggetions you may have.Fri, 21 Nov 2008 09:09:05 GMTlrosales