Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

GetDate Function Expand / Collapse
Author
Message
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, July 16, 2014 6:39 AM
Points: 77, Visits: 425
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
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 9, 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
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, July 16, 2014 6:39 AM
Points: 77, Visits: 425
Did You test it?
Yes I did tried it.

thanks




"We never plan to Fail, We just fail to plan":)
Post #606649
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, July 16, 2014 6:39 AM
Points: 77, Visits: 425
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
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: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
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
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: Monday, June 23, 2014 2:21 AM
Points: 1,205, Visits: 921
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

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #608876
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: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
Since when can DateofBirth = "" ???


A date value is set to a date.... or null, period (sql server side).
Post #608892
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: Monday, June 23, 2014 2:21 AM
Points: 1,205, Visits: 921
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

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #609677
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: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
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
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: Monday, June 23, 2014 2:21 AM
Points: 1,205, Visits: 921

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

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #609858
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse