Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Development » GetDate Function
Add to briefcase

GetDate Function Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
lrosales
Posted Friday, November 21, 2008 9:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 12:42 PM
Points: 77, Visits: 396
guys
this 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 personal
go

create table tt99
(
Datetime2 smalldatetime default (Getdate()) ,
Description nvarchar(200)
)

insert into tt99(Datetime2, description)
values ('12/20/08','Testing the Default')
go
insert into tt99(Datetime2, description)
values ('','Testing the Default')

select * from tt99

drop table tt99

here are the results:

2008-12-20 00:00:00 Testing the Default
1900-01-01 00:00:00 Testing the Default

the last Record is the Problem.

Thanks for any suggetions you may have.



"We never plan to Fail, We just fail to plan":)
Post #606618
KenSimmons
Posted Friday, November 21, 2008 9:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:07 AM
Points: 1,221, Visits: 2,614
In 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')
go
insert into tt99( description)
values ('Testing the Default')

select * from tt99

drop table tt99

Ken Simmons
http://twitter.com/KenSimmons
Post #606645
lrosales
Posted Friday, November 21, 2008 9:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 12:42 PM
Points: 77, Visits: 396
Did You test it?
Yes I did tried it.

thanks



"We never plan to Fail, We just fail to plan":)
Post #606649
lrosales
Posted Friday, November 21, 2008 11:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 12:42 PM
Points: 77, Visits: 396
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



"We never plan to Fail, We just fail to plan":)
Post #606737
Ninja's_RGR'us
Posted Monday, November 24, 2008 5:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 5:19 AM
Points: 21,357, Visits: 9,530
Well 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.
Post #607442
Manie Verster
Posted Wednesday, November 26, 2008 12:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 1,151, Visits: 878
lrosales (11/21/2008)
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

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:
insert into tt99(datetime2, description)
        values (NULL, 'Testing the Default')

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.
case when DateofBirth = "" then NULL else DateofBirth end

That's just my bit of advice.

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today
Post #608876
Ninja's_RGR'us
Posted Wednesday, November 26, 2008 1:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 5:19 AM
Points: 21,357, Visits: 9,530
Since when can DateofBirth = "" ???


A date value is set to a date.... or null, period (sql server side).
Post #608892
Manie Verster
Posted Thursday, November 27, 2008 4:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 1,151, Visits: 878
Ninja's_RGR'us (11/26/2008)
Since when can DateofBirth = "" ???


A date value is set to a date.... or null, period (sql server side).


Don't bite my head of now! I meant 
case when DateofBirth = '' then NULL else DateofBirth end

. 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.

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today
Post #609677
Madhivanan-208264
Posted Thursday, November 27, 2008 4:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:17 AM
Points: 329, Visits: 461
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/02/empty-string-and-default-values.aspx


Madhivanan

Failing to plan is Planning to fail
Post #609693
Manie Verster
Posted Thursday, November 27, 2008 8:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 1,151, Visits: 878
Madhivanan (11/27/2008)
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/02/empty-string-and-default-values.aspx

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.

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today
Post #609858
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.