Datetime and NULL's | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Datetime and NULL’s

Hi, I have table that has a datetime field and this field accepts NULL values, if a user doesn’t enter a value I capture this in the stored procedure like so: @END_DATE DATETIME= NULL This seems to work fine, but if I try to delete or update the record I then receive the error: Microsoft OLE DB Provider for SQL Server (0x80040E07)
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
This table also has a Trigger that updates another table, and I have traced the problem to be a line in the trigger: isnull(END_DATE,convert(char(10),getdate(),103)),
I am only guessing but I am assuming that this trigger is not recognising the field value as NULL. Any ideas on what I am doing wrong would be great Thanks in advance
Peter
Why are you converting to CHAR(10) anyway? Frank
http://www.insidesql.de
http://www.familienzirkus.de
Now it hit me. Could this be an issue with your data format?
I bet the conversion to 103 does cause the trouble.
Frank
http://www.insidesql.de
http://www.familienzirkus.de
Yes, try not use conversion. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

declare @date datetime
select @date = NULL
— select isnull(@date,convert(char(10),getdate(),103)) this will return an error
try
select convert(char(10), isnull(@date,getdate()),103)

Actually where is the difference?
create table mytest(
a datetime,
b smalldatetime)
declare @date datetime
set @date=NULL
INSERT INTO mytest (a,b) values(convert(char(10), isnull(@date,getdate()),103),convert(char(10),
isnull(@date,getdate()),103))
INSERT INTO mytest (a,b) values(isnull(@date,getdate()),
isnull(@date,getdate()))
select * from mytest
drop table mytest a b
—————————————————— ——————–
2003-12-18 00:00:00.000 2003-12-18 00:00:00
2003-12-18 13:46:45.010 2003-12-18 13:47:00 (2 row(s) affected)
but in stripping off the time portion?
I tend to use convert when actually presenting the data. Frank
http://www.insidesql.de
http://www.familienzirkus.de
select isnull(@date, convert(char(10),getdate(),103)) => isnull(datetime, var/char) select convert(char(10), isnull(@date,getdate()),103) => isnull(datetime, datetime) From BOL: Syntax
ISNULL ( check_expression , replacement_value ) Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type. replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.

Sorry bambola, but I think you’ve missed my point.
I think the whole convert stuff is not necessary at all.
The original poster is trying to insert a datetime value from one table into another. So why converting? Frank
http://www.insidesql.de
http://www.familienzirkus.de
You are absolutely right, Frank. There is no need to convert the datetime char while inserting a value to a table. I missed that <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />
]]>