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


GetDate Function


GetDate Function

Author
Message
lrosales
lrosales
Right there with Babe
Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)

Group: General Forum Members
Points: 723 Visits: 428
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"Smile
KenSimmons
KenSimmons
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3812 Visits: 2614
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
lrosales
lrosales
Right there with Babe
Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)

Group: General Forum Members
Points: 723 Visits: 428
Did You test it?
Yes I did tried it.

thanks

"We never plan to Fail, We just fail to plan"Smile
lrosales
lrosales
Right there with Babe
Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)Right there with Babe (723 reputation)

Group: General Forum Members
Points: 723 Visits: 428
Guys!
I solved the problem. the answer is simply "Default"

insert into tt99(Datetime2, description)
values (default,'Testing the Default')

simple is it not?

Tongue

"We never plan to Fail, We just fail to plan"Smile
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116764 Visits: 9672
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 Tongue.



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
Manie Verster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3157 Visits: 1044
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?

Tongue

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
Ninja's_RGR'us
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116764 Visits: 9672
Since when can DateofBirth = "" ???


A date value is set to a date.... or null, period (sql server side).
Manie Verster
Manie Verster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3157 Visits: 1044
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.
HeheHeheHeheHehe

:-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
Madhivanan-208264
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 476
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
Manie Verster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3157 Visits: 1044

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

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