Casting datetime to varchar in scalar-valued function SQL Server 2005

I’m trying to get the following statement executed. The scalar-valued function is expecting a varchar for the date and the original column in customertable is a datetime. I’ve done my research and looked around; the various solutions I’ve tried haven’t worked.

When I run this:

select 
   ID, DATE_COL,
   dbo.CustRandValuationOnDate (ID, LEFT(CONVERT(VARCHAR, DATE_COL, 120), 10)) 
from customertable

​x
 
select    ID, DATE_COL,   dbo.CustRandValuationOnDate (ID, LEFT(CONVERT(VARCHAR, DATE_COL, 120), 10)) from customertable​

I get this error:

Conversion failed when converting character string to smalldatetime data type.

UPDATE: function code

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER FUNCTION [dbo].[CustRandValuationOnDate](@DATE VARCHAR(11), @CIFNUMBER VARCHAR(12)) RETURNS FLOAT
 AS BEGIN
 RETURN ISNULL((SELECT SUM(DBO.RandValOfAccOnDate(@DATE, A.ACCOUNTID))
         FROM   ACCOUNTTBL A, BALANCETBL B
         WHERE  A.CIFNUMBER=@CIFNUMBER
         AND    B.ACCOUNTID=A.ACCOUNTID
         AND    B.VALUATIONDATE=@DATE), 0.00);
 END

 
GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO ALTER FUNCTION [dbo].[CustRandValuationOnDate](@DATE VARCHAR(11), @CIFNUMBER VARCHAR(12)) RETURNS FLOAT AS BEGIN RETURN ISNULL((SELECT SUM(DBO.RandValOfAccOnDate(@DATE, A.ACCOUNTID))         FROM   ACCOUNTTBL A, BALANCETBL B         WHERE  A.CIFNUMBER=@CIFNUMBER         AND    B.ACCOUNTID=A.ACCOUNTID         AND    B.VALUATIONDATE=@DATE), 0.00); END​

UPDATE: some of the values in DATE_COL are null. ive just confirmed that.

Answer

You are passing your params in the wrong order to function..

Advertisement

Answer