September 10, 2008 at 2:05 am
I wonder about the following:
I'm reviewing a database and notice all table-attributes are NULLable, which is not best practice IMO. When checking potential problems when making the attributes not NULLable I noticed an empty datetime attribute (value '') will return a date: 1900-01-01. In some situations no date should be visible.
Ofcourse I can remove all dates in a SELECT statement with a certain value, but I wonder: can I make a datetime attribute not NULLable AND prevent from an empty value to be converted to a date?
Greetz,
Hans Brouwer
September 10, 2008 at 2:13 am
FreeHansje (9/10/2008)
I wonder about the following:I'm reviewing a database and notice all table-attributes are NULLable, which is not best practice IMO. When checking potential problems when making the attributes not NULLable I noticed an empty datetime attribute (value '') will return a date: 1900-01-01. In some situations no date should be visible.
Ofcourse I can remove all dates in a SELECT statement with a certain value, but I wonder: can I make a datetime attribute not NULLable AND prevent from an empty value to be converted to a date?
Hi Hans,
there is nothing like "empty datetime attribute". The datetime data type stores datetimes. If the column is nullable you can store nulls. Everything else will have to be of the type datetime. You can only insert datetimes, etc. When you insert an empty string (''), SQL Server will cast it to a datetime, and it will be, as you experience it 1900-01-01. (you can check this by executing: select cast ('' as datetime)). Once the date has been inserted, it will be just a datetime value, and the information about the original type from which you have converted will be lost forever.
You can use an extremal value (1900-01-01 or 1911-11-11), and check for these explicitly, however this is exactly what null values are for.
Regards,
Andras
September 10, 2008 at 4:37 am
Tnx for answering, Andras. I was afraid of this.
Oh well, just have to live with this.
Greetz,
Hans Brouwer
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply