这个功能不能给出日期差异 [英] this function can not give date diffrence

问题描述

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




ALTER   FUNCTION dbo.GetExpiryTime(@ProductID as varchar(10), @BatchNo as varchar(15), @CurrentDate as smalldatetime)
RETURNS numeric(7,0)
AS
BEGIN 

	Declare @Opening smalldatetime
	Declare @Purchase smalldatetime
	Declare @Day numeric(7,0)
--	set @Day =

	IF @BatchNo is null
	BEGIN 	
		select @Opening = ExpiryDate from OpeningStock
		where productid = @ProductID 
		And BatchNo is null

		select @Purchase = ExpiryDate from purchasebody 
		where productid = @ProductID 
		And BatchNo is Null
	END
	else
	BEGIN 
		select @Opening = ExpiryDate from OpeningStock
		where productid = @ProductID 
		And BatchNo = @BatchNo 

		select @Purchase = ExpiryDate from purchasebody 
		where productid = @ProductID 
		And BatchNo = @BatchNo 
	END
		
	IF @Opening  is not null
	BEGIN 
		Select @Day = isnull(min(DATEDIFF (day, @CurrentDate, @Opening)),0)
	END
	IF @Purchase is  not null 
	beGIN 
		Select @Day = isnull(min(DATEDIFF (day, @CurrentDate, @Purchase)),0)
	END
	Return isnull(@Day ,0)

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

推荐答案

好吧，我有桌面购买者好吧保存expirydate到puerchasebody表好像日期31-10-2106函数sql



然后执行函数选择dbo.GetExpiryTime（1,123 ，getdate（））



然后日期差异显示剩余365天到期产品但此功能显示0



现在你明白了







SELECT DATEDIFF（day，'2014-08-05'， '2014-06-05'）AS DiffDate就像这样显示61天我想要的差异功能



嗯。



你看过你的存储过程吗？所有？

这将产生什么价值？

"okay i have table purchasebody okay save expirydate into puerchasebody table okay like date 31-10-2106 function sql

then execute function select dbo.GetExpiryTime(1, 123, getdate())

then date diffrence show 365 day left to expiry product but this function show 0

now you understand



SELECT DATEDIFF(day,'2014-08-05','2014-06-05') AS DiffDate like this this is show diffrence 61 day i want this in function"

Um.

Did you look at your stored procedure at all?
What value is this going to generate?

MIN(61, 0)

你的意思是MAX吗？

Did you perhaps mean "MAX" instead?

这篇关于这个功能不能给出日期差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！