How do I assign NULL values to a DATE variable in SQL SERVER 2005?
-
Hi, This is a part of my Stored proc where I am trying to assign NULL values to the date variables ALTER PROCEDURE [dbo].[USP_SearchVehicleOnDateRange] @DateFrom DateTime=null ,@DateTo DateTime=null AS BEGIN SET NOCOUNT ON; IF LEN(@DateFrom)<=0 begin select @DateFrom = NULL end IF LEN(@DateTo)<=0 begin select @DateTo = NULL end ..... ... But when I execute this its throwing the below error. Msg 241, Level 16, State 1, Procedure USP_SearchVehicleOnDateRange, Line 16 Conversion failed when converting datetime from character string. Any clue on how assign a NULL value to the data field? Thanks -L
-
Hi, This is a part of my Stored proc where I am trying to assign NULL values to the date variables ALTER PROCEDURE [dbo].[USP_SearchVehicleOnDateRange] @DateFrom DateTime=null ,@DateTo DateTime=null AS BEGIN SET NOCOUNT ON; IF LEN(@DateFrom)<=0 begin select @DateFrom = NULL end IF LEN(@DateTo)<=0 begin select @DateTo = NULL end ..... ... But when I execute this its throwing the below error. Msg 241, Level 16, State 1, Procedure USP_SearchVehicleOnDateRange, Line 16 Conversion failed when converting datetime from character string. Any clue on how assign a NULL value to the data field? Thanks -L
If I'm not mistakem, LEN is used for strings only, so if you want to know if that parameter is NULL, simply ask if @DateFrom = NULL begin ... end Please check if that works for you daniero
-
If I'm not mistakem, LEN is used for strings only, so if you want to know if that parameter is NULL, simply ask if @DateFrom = NULL begin ... end Please check if that works for you daniero
SQL Server Books Online IS [NOT] NULL Determines whether or not a given expression is NULL. Syntax expression IS [ NOT ] NULL Remarks To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL. ---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Hi, This is a part of my Stored proc where I am trying to assign NULL values to the date variables ALTER PROCEDURE [dbo].[USP_SearchVehicleOnDateRange] @DateFrom DateTime=null ,@DateTo DateTime=null AS BEGIN SET NOCOUNT ON; IF LEN(@DateFrom)<=0 begin select @DateFrom = NULL end IF LEN(@DateTo)<=0 begin select @DateTo = NULL end ..... ... But when I execute this its throwing the below error. Msg 241, Level 16, State 1, Procedure USP_SearchVehicleOnDateRange, Line 16 Conversion failed when converting datetime from character string. Any clue on how assign a NULL value to the data field? Thanks -L
First off - your error is on line 16, and you have only showed us 14 lines of the stored procedure. Secondly, you should use IS NULL and not LEN() to check if a variable contains a null value.
IF @DateFrom IS NULL BEGIN 'Do something (but why set @DateFrom to NULL, since it defaults to NULL above???) END---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters -
First off - your error is on line 16, and you have only showed us 14 lines of the stored procedure. Secondly, you should use IS NULL and not LEN() to check if a variable contains a null value.
IF @DateFrom IS NULL BEGIN 'Do something (but why set @DateFrom to NULL, since it defaults to NULL above???) END---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. PetersI had an idea I could be wrong, but I didn't have some place to make sure (developing PC) at the time. Thanks for correcting me. daniero