CODEWITHSUNDEEP
sqlsql-serversql-server-2005type-conversion
Mina
Mina

Reputation: 747

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:

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

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

Upvotes: 0

Views: 1831

Answers (5)

Terence
Terence

Reputation: 16

You should try

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

Upvotes: 0

KM.
KM.

Reputation: 103667

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?

Upvotes: 1

Terence
Terence

Reputation: 16

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

Upvotes: 0

Marco Hurtado
Marco Hurtado

Reputation: 534

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.

Upvotes: 0

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

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

Upvotes: 0

Related Questions