Link to home
Start Free TrialLog in
Avatar of bruno_boccara
bruno_boccaraFlag for France

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),dateadd(yy,@NBRANNEE,GETDATE())+1,103) as smalldatetime) AS [AUJOURDHUI+X ANNEE])
END

thanks you for your help !

regards .
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Hi Bruno,
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
Avatar of Vitor Montalvão
Why are you converting twice? Dateadd() already returns a datetime value.
This should be enough:
RETURN dateadd(yy,@NBRANNEE,GETDATE()+1)
Avatar of bruno_boccara

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),dateadd(yy,@NBRANNEE,GETDATE())+1,103) as smalldatetime) AS [AUJOURDHUI+X ANNEE])
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."
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
it works fine.
thanks.