July 30, 2003 at 1:37 pm
If I insert an empty string into a datetime field it becomes '1/1/1990' --the "lower limit" for a smalldatetime field.
(The "limit limt" for a datetime field is January 1, 1753.)
Any idea why this happens?
TIA,
Bill
July 30, 2003 at 4:11 pm
BOL 2000 :
There are no separate time and date data types for storing only times or only dates. If only a time is specified when setting a datetime or smalldatetime value, the date defaults to January 1, 1900. If only a date is specified, the time defaults to 12:00 A.M. (Midnight).
Conclusion : if none is specified both default to the lower limits.
Declare @d datetime
Set @d=''
Select @d
Set @d=Null
Select @d
July 31, 2003 at 2:04 am
If you write zero to a datetime it will be represented as 1/1/1900, so something like this may be what is occurring.
Try forcing the blank to be null, for example by using the NULLIF function: NULLIF(@MyDateVarChar, '')
July 31, 2003 at 9:01 am
A work-around would be to
create a view of the original data, but casting the date to varchar, and substituting '' where null.
CREATE VIEW BlankDate as
select . . . isnull(CAST (date_col AS VARCHAR(20)),'') 'CleanDate'
from myTable
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply