bruno_boccara
asked on
problem with scalar function in MSSQL
Hello,
I have an error message when trying to create a function that returns the following day in X years.
(Column, parameter or variable #0 : datatype SMALLDATETIME not found
what's wrong with this syntax ?
CREATE FUNCTION [AUJOURDHUI + X AN](@NBRANNEE INT) RETURNS SMALLDATIME
AS
BEGIN
RETURN (SELECT cast(convert(varchar(12),d ateadd(yy, @NBRANNEE, GETDATE()) +1,103) as smalldatetime) AS [AUJOURDHUI+X ANNEE])
END
thanks you for your help !
regards .
I have an error message when trying to create a function that returns the following day in X years.
(Column, parameter or variable #0 : datatype SMALLDATETIME not found
what's wrong with this syntax ?
CREATE FUNCTION [AUJOURDHUI + X AN](@NBRANNEE INT) RETURNS SMALLDATIME
AS
BEGIN
RETURN (SELECT cast(convert(varchar(12),d
END
thanks you for your help !
regards .
Why are you converting twice? Dateadd() already returns a datetime value.
This should be enough:
RETURN dateadd(yy,@NBRANNEE,GETDA TE()+1)
This should be enough:
RETURN dateadd(yy,@NBRANNEE,GETDA
ASKER
I know that's it's not the best way, but I converted twice to obtain the end of the following day.
if today we are the 06/22/2015, it gives me 06/23/yyyy.
look the following , it works when it is a simple select, but not in a scalar function ...
DECLARE @NBRANNEE INT
SET @NBRANNEE = -1
(SELECT cast(convert(varchar(12),d ateadd(yy, @NBRANNEE, GETDATE()) +1,103) as smalldatetime) AS [AUJOURDHUI+X ANNEE])
if today we are the 06/22/2015, it gives me 06/23/yyyy.
look the following , it works when it is a simple select, but not in a scalar function ...
DECLARE @NBRANNEE INT
SET @NBRANNEE = -1
(SELECT cast(convert(varchar(12),d
Hi Bruno,
When I try that exact last statement you posted I get the following error:
"The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value."
When I try that exact last statement you posted I get the following error:
"The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value."
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it works fine.
thanks.
thanks.
I think that one problem may be with the format of the datestring that the CAST .. SMALLDATETIME is getting
The following throws a conversion error:
SELECT CAST('23/06/2016' AS smalldatetime)
whereas the following is OK
SELECT CAST('2016-06-23' AS smalldatetime)
so I think that maybe if you do a CONVERT(...,120) you should get the '2016-06-23' format and this may get you the result you need