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

I get this error:

Conversi开发者_Python百科on 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

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

OP says DATE is a datetime so:

LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10)

is valid and works, as does:

LEFT(CONVERT(VARCHAR, NULL, 120), 10)

As a result, the error must be in your dbo.CustRandValuationOnDate() function. Without that code, there is no way to solve this. Perhaps, dbo.CustRandValuationOnDate() needs to be written to handle NULL input values.

EDIT after function code posted:

RETURN ISNULL((SELECT SUM(DBO.RandValOfAccOnDate(@DATE, A.ACCOUNTID))
                                                ^^ID?   ^^date??

parameters in wrong order?

you define the function parameters as: @DATE VARCHAR(11), @CIFNUMBER VARCHAR(12), but call it with ID, LEFT(CONVERT(VARCHAR, DATE_COL, 120), 10), so what are you really trying to do?

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

Instead for Converting in to Date you can Use Cast(YourVariable As Date) for converting in to DateFields.

Maybe you need to re-write your scalar function dbo.CustRandValuationOnDate to recive a datetime. after in this function, first validate it is null.

After, make the cast to varchar and use the LEFT function.

You should try

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