remove sql server date time default value

Question

remove sql server date time default value

pulak_mca

Mr or Mrs. 500

Points: 505
More actions
November 12, 2008 at 12:07 am

#201098

when we enter blank in the coloumn which type is date time it takes default value as 01/01/1900:00.00.00 ...how can i put it blank in the table

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

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 1

Look up datetime in BOL for an explanation of why, it's actually returning a value that is compared to 1/1/1900 00:00:00.

You should either pick a different default date for blank fields (e.g. '12/31/2078 00:00:00') that you know will not be used and change to that date on insert when an empty string ('') is entered, or when selecting data, replace the '1/1/1900 00:00:00' with an empty string.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Manie Verster SSCertifiable Points: 7152 More actions · Answer 2

jcrawf02 (11/13/2008)
Look up datetime in BOL for an explanation of why, it's actually returning a value that is compared to 1/1/1900 00:00:00.
You should either pick a different default date for blank fields (e.g. '12/31/2078 00:00:00') that you know will not be used and change to that date on insert when an empty string ('') is entered, or when selecting data, replace the '1/1/1900 00:00:00' with an empty string.

Datetime is a tricky datatype. Whenever you pass a null or an empty string you will always get a default date. jcrawf02 is right when he said that you should use a date that you know will never be used and when the data gets passed to the UI you must just write some code to get an empty string in the data field.

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 3

This is not specific to the Compact Edition. It has to do with the date/time object itself. The SQL object uses an offset from an origin to store the date. That origin is 1/1/1900. Try UPDATE MyTable SET MyDate=0 and see what happens.

If you have to deal with dates before 1900 your application might not scale that well with the Compact Edition. My apps render 1/1/1900 00:00:00 as blank. Best to store unknown dates as NULL anyway.

ATBCharles Kincaid

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Charles Kincaid (11/29/2008)
Best to store unknown dates as NULL anyway.

I agree... if you don't know a date, store it as a NULL and convert it in the GUI.

Some folks store unknown or "open" dates as 99991231 so they don't have to check for null to see if an enddate has occurred in the past or will occur in the future. n Not sure I agree with that method, but you could program the GUI to return a blank when you see that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Charles Kincaid SSChampion Points: 13593 More actions · Answer 5

Jeff Moden (11/29/2008)

Some folks store unknown or "open" dates as 99991231 so they don't have to check for null to see if an enddate has occurred in the past or will occur in the future. n Not sure I agree with that method, but you could program the GUI to return a blank when you see that.

You have changed my thinking on several good points and I thank you for that. I strongly contend that there is very little difference between 99991231 and 19000101. One is the maximum permissible date and the other is the minimum. I've done the trick of dealing with zero dates (19000101) in the UI as it was, at the time, easier than dealing with NULLs.

ATBCharles Kincaid

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 6

Charles Kincaid (12/1/2008)

Jeff Moden (11/29/2008)
Some folks store unknown or "open" dates as 99991231 so they don't have to check for null to see if an enddate has occurred in the past or will occur in the future. n Not sure I agree with that method, but you could program the GUI to return a blank when you see that.
You have changed my thinking on several good points and I thank you for that. I strongly contend that there is very little difference between 99991231 and 19000101. One is the maximum permissible date and the other is the minimum. I've done the trick of dealing with zero dates (19000101) in the UI as it was, at the time, easier than dealing with NULLs.

So to sum up, Jeff says the glass could be half full, Charles says the glass could be half empty, but neither one of them really believes the water even exists . . .

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Charles Kincaid (12/1/2008)

Jeff Moden (11/29/2008)
Some folks store unknown or "open" dates as 99991231 so they don't have to check for null to see if an enddate has occurred in the past or will occur in the future. n Not sure I agree with that method, but you could program the GUI to return a blank when you see that.
You have changed my thinking on several good points and I thank you for that. I strongly contend that there is very little difference between 99991231 and 19000101. One is the maximum permissible date and the other is the minimum. I've done the trick of dealing with zero dates (19000101) in the UI as it was, at the time, easier than dealing with NULLs.

The zero date (19000101) works regardless of data type (datetime or smalldatetime) whereas 99991231 only works for datetime, not smalldatetime. Unfortunately, I don't remember off-hand what the maximum date is for a smalldatetime.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 8

jcrawf02 (12/1/2008)

So to sum up, Jeff says the glass could be half full, Charles says the glass could be half empty, but neither one of them really believes the water even exists . . .

Jeff empties out the data "Row by row" and I'm usually just full of it. :laugh:

ATBCharles Kincaid

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Charles Kincaid (12/1/2008)

jcrawf02 (12/1/2008)
So to sum up, Jeff says the glass could be half full, Charles says the glass could be half empty, but neither one of them really believes the water even exists . . .
Jeff empties out the data "Row by row" and I'm usually just full of it. :laugh:

Now, I don't care who you are... that's funny, right there. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

jcrawf02 (12/1/2008)

Charles Kincaid (12/1/2008)
Jeff Moden (11/29/2008)
Some folks store unknown or "open" dates as 99991231 so they don't have to check for null to see if an enddate has occurred in the past or will occur in the future. n Not sure I agree with that method, but you could program the GUI to return a blank when you see that.
You have changed my thinking on several good points and I thank you for that. I strongly contend that there is very little difference between 99991231 and 19000101. One is the maximum permissible date and the other is the minimum. I've done the trick of dealing with zero dates (19000101) in the UI as it was, at the time, easier than dealing with NULLs.
So to sum up, Jeff says the glass could be half full, Charles says the glass could be half empty, but neither one of them really believes the water even exists . . .

Well, kinda... if you have a StartDate and no EndDate, which is easier to handle? 19000101, NULL, or 99991231? Even though I prefer "NULL" because the EndDate is "unknown", I can see some very strong merit in using 99991231 because it's very simple thrying to figure out if "today" (or any date) is between the StartDate and EndDate. Typically, no EndDate means something hasn't ended yet... instead of a WHERE clause like...

WHERE somedate >= StartDate

AND (somedate < EndDate or EndDate IS NULL)

... you can simply get away with ...

WHERE somedate >= StartDate

AND somedate < EndDate

Of course, you do need a NOT NULL constraint on EndDate.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)