Problem with Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with Stored Procedure

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 07:30:20

Hello,

I am getting the following error in SQL and cannot work out why.

"Error 257: Implicit conversion from data type datetime to in is not allowed. Use the CONVERT function to run this query."

The SQL for this Stored Procedure is very straightforward :


"CREATE PROCEDURE [dbo].[sp_GetHolidayYearStartDate]

@HolidayDate datetime,
@Returned datetime OUTPUT

AS

SELECT @Returned =  dbo.fn_YearBreakdownDateFrom(@HolidayDate)
RETURN @Returned"

The function being used is again very straightforward:


"CREATE FUNCTION dbo.fn_YearBreakdownDateFrom
( 
  @Date datetime
) 
RETURNS datetime
AS
BEGIN 
  DECLARE	@RetVal datetime
  
  SET @RetVal = (SELECT  DateFrom
  FROM      dbo.tbl_YearBreakdown
  WHERE     (DateFrom <= CONVERT(DATETIME, @Date, 103)) AND
            (DateTo >= CONVERT(DATETIME, @Date, 103)))
    
  RETURN @RetVal
END"

Can anyone help please ?

Thanks,

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 07:48:03

Whats the data type of DateFrom and DateTo ?

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 07:53:56

Hi sakets_2000 - These are both set as 'datetime'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-23 : 08:03:48

Try


CREATE FUNCTION dbo.fn_YearBreakdownDateFrom
( 
  @Date datetime
) 
RETURNS datetime
AS
BEGIN 
  DECLARE	@RetVal datetime
  
  SET @RetVal = (SELECT  DateFrom
  FROM      dbo.tbl_YearBreakdown
  WHERE     (DateFrom >= dateadd(day,datediff(day,0,@Date),0) AND
            (DateTo >= dateadd(day,datediff(day,0,@Date),1))))
    
  RETURN @RetVal
END

Madhivanan

Failing to plan is Planning to fail

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 08:16:10

Hi Madhivanan,

I have done as you suggested, but this still produces the same error.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 08:54:39

how are you running the sp ? Can you post the exact code ?

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 09:05:03

That's just it - I can't even get to running the code as yet.

I have the function set up, and if I enter the following in Query Analyser :

SELECT fn_YearBreakdownDateFrom ('23/02/2009')

I get a value of "2009-01-01 00:00:00.000" as I'd expect.

I then try to add the stored procedure to call this function, and I can't save as I get the error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-23 : 09:12:50

[code]ALTER FUNCTION dbo.fn_YearBreakdownDateFrom
(
@Date DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN (
SELECT DateFrom
FROM dbo.tbl_YearBreakdown
WHERE DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0) BETWEEN DateFrom AND DateTo
)
END[/code]

E 12°55'05.63"
N 56°04'39.26"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 09:26:10

Hi Peso,

I've tried changing the function to the code you suggested, and it works fine.
However, when I try to save my stored procedure I get the same message.

If I change the @Returned variable to something like varchar, I don't get this error and it will let me save it, but obviously this is no good to me as I need the value as a date.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 09:48:05

try this,

CREATE PROCEDURE [dbo].[sp_GetHolidayYearStartDate] @HolidayDate datetime
AS
Begin
RETURN dbo.fn_YearBreakdownDateFrom(@HolidayDate)
end

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 09:59:49

Hi sakets_2000 - I'm sorry to say I get exactly the same message.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 10:02:25

You get the error while compiling the sp ?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 10:02:48

Also post the result of this,
sp_help YearBreakdownDateFrom

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 10:09:24

Yes - When I'm in the sp and I try to 'Check Syntax' or click 'Ok' I get the error.

Also, when I type that sp_help thing, I get :

Name Owner Type Created_Datetime
fn_YearBreakdownDateFrom dbo scalar function 2009-01-05 15:20:51.867

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 10:14:59

Am sorry, can you post result of

sp_help tbl_YearBreakdown

Also, Just try this once,,,

ALTER FUNCTION dbo.fn_YearBreakdownDateFrom
( 
  @ssDate datetime
) 
RETURNS datetime
AS
BEGIN 
  DECLARE	@RetVal datetime
  
  SET @RetVal = (SELECT  DateFrom
  FROM      dbo.tbl_YearBreakdown
  WHERE     (DateFrom <= CONVERT(DATETIME, @ssDate, 103)) AND
            (DateTo >= CONVERT(DATETIME, @ssDate, 103)))
    
  RETURN @RetVal
END

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 10:21:30

No problem - here you go.

Name Owner Type Create_Datetime
tbl_YearBreakdown dbo user table 2009-01-05 15:22:16.663

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collate
ID bigint no 8 no (n/a) (n/a) NULL
DateFrom datetime no 8 no (n/a) (n/a) NULL
DateTo datetime no 8 no (n/a) (n/a) NULL

Identity Seed Increment Not For Replication
ID 1 1 0

RowGuidCol
No rowguidcol column defined.

Data_located_on_filegroup
PRIMARY

Index_name Index_description Index_keys
PK_tbl_YearDetails clustered, unique, primary key located on PRIMARY ID

Constraint Type constraint_name delete_action update_action status_enabled status_for_replication
PRIMARY KEY (clustered) PK_tbl_YearDetails (n/a) (n/a) (n/a) (n/a) ID

Also, when I tried your code above it worked fine, but again the sp wouldn't let me save it as I got the same error.

Thanks,

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 10:35:23

Return codes from sp can only be integer value.

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 10:41:09

So how can I get round this - basically I want to call this stored procedure from my VB application and have it produce a date.

Is this possible ?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 10:42:07

Why not call the function straight away ?

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-23 : 10:51:27

I'm not really sure how to do this to be honest - if I can though, I'll look into it.

Thank you for all of your help.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 10:54:35

You can call like this,

exec dbo.fn_YearBreakdownDateFrom @date='20090101'

OR

declare @date datetime
set @date='20090101'
exec dbo.fn_YearBreakdownDateFrom @date

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources