Default value still inserts NULL values 2

[vnad]

I have a column that has a default property set to getDate() on a DateTime column. When a NULL value comes in, it still populates the field as NULL and not the current date and time. Any ideas?

 

[SQLSister]

IF the data is sent to it to specifically insert a null value, that's what you'll get. Suggest your insert staement simply not include the columns that are null or have your user interface code insert the current date as the value for the field if this is not possible.

 

[vnad]

The insert does not include data for that column when inserting. We also have no control over the way the data is inserted since it is a third party application.

 

[cheyney]

try setting the Allows Null property of the column to false

cheyney

 

[topher0303]

Are you sure the statement is not inserting a null? Do you have the code? or are you saying the field is not on a screen (assuming there is a UI)?

 

[Transcend]

If you have no control over the way the data is inserted then it is likely that null is being inserted.

If you set it to not allow null and then the program tries to you'll get an error (which they'll have to fix)

Transcend

 

[vnad]

There is a possiblitly that it is inserting spaces instead of a date. Would this get past the check to see if it is null, not insert a default value, then fail on a datetime format and insert a null?

 

[SQLSister]

I tried inserting the empty string and it inserted 1/1/1900 as the date even though I had a default set.
You could put an instead of trigger on the thing and have it check to see what the value of the field is and insert current date then.

 

[bertrandkis]

Say your table is Footable with Col1 and Col2. Now Col2 is the column where you want the date to be getdate() even when the client App sends a null value.

You can Create a trigger as shown below:

Create trigger trgDefaultdate
On Footable for INSERT
AS
BEGIN
--Prevent trigger from firing for bulk insert
if(( select count(*) from Inserted)=1)
Begin
if((select col2 from inserted) is null or (select col2 from inserted)='Null')
--Replace the null entry with the date
update footable set col2=getdate() where col1=(select col1 from inserted)
end
END
NOTE: The column you use in your where clause (here col1 is used) must be a Primarykey colum or a candidate key column (column that can uniquely idenitify rows of your table to avoid updating existing rows that have valid records)

Hope this helps. I you need more help in this regard let me know.

Cheers.
Bertrandkis 'The black belt SQL Programmer'

 

[vnad]

We inserted spaces for the datetime field and it did populate as a NULL value in the database. We are going to either try and set it so it sends a NULL value instead of spaces or setup a trigger to correct it on the SQL side of things. Thanks