GetDate Function

Question

GetDate Function

lrosales

SSCommitted

Points: 1821
More actions
November 21, 2008 at 9:09 am

#94841

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
(
Datetime2smalldatetime default (Getdate()) ,
Descriptionnvarchar(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:00Testing the Default
1900-01-01 00:00:00Testing 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":)

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

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

lrosales SSCommitted Points: 1821 More actions · Answer 2

Did You test it?

Yes I did tried it.

thanks

"We never plan to Fail, We just fail to plan":)

lrosales SSCommitted Points: 1821 More actions · Answer 3

Guys!

I solved the problem. the answer is simply "Default"

insert into tt99(Datetime2, description)

values (default,'Testing the Default')

simple is it not?

😛

"We never plan to Fail, We just fail to plan":)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

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.

Manie Verster SSCertifiable Points: 7152 More actions · Answer 5

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?
😛

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.

:-PManie 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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Since when can DateofBirth = "" ???

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

Manie Verster SSCertifiable Points: 7152 More actions · Answer 7

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.

:hehe::hehe::hehe::hehe:

:-PManie 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)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 8

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/02/empty-string-and-default-values.aspx

Madhivanan

Failing to plan is Planning to fail

Manie Verster SSCertifiable Points: 7152 More actions · Answer 9

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

:-PManie 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)