how to compare two dates in stored procedure?

[jack1257]

In this procedure im comparing two dates. Its the stored procedure could be executed. Im face an problem when executing this stored procedure.

Error:exec sp_valid
@coupon_no = 101



Msg 242, Level 16, State 3, Procedure sp_valid, Line 13
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Msg 241, Level 16, State 1, Procedure sp_valid, Line 17
Conversion failed when converting datetime from character string.




<code>set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[sp_valid]
(
@coupon_no int
)
as
begin
if not exists(select couponno from coupons where couponno = @coupon_no)
select 'the coupon no is incorrect'
declare @issue_date DATETIME
select @issue_date = validupto from coupons where couponno = @coupon_no
declare @valid_upto Datetime
select @valid_upto = validupto from coupons where couponno = @coupon_no
select convert (int, convert (datetime,'99995862 23:59:59:997'))
if ((@issue_date) > (@valid_upto))
select 'the coupon is expire'
else
select 'the coupon will expires on ' + @valid_upto
end </code>



Table code:

<code>
USE [vvit]
GO
/****** Object: Table [dbo].[coupons] Script Date: 07/17/2010 11:15:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[coupons](
[couponno] [int] NULL,
[issuedate] [datetime] NULL CONSTRAINT [DF_coupons_issuedate] DEFAULT (getdate()),
[validupto] AS ([issuedate]+(30))
) ON [PRIMARY]

</code>

[Delerna]

can you explain what you are trying to do with this line ?

select convert (int, convert (datetime,'99995862 23:59:59:997'))
 

The error is resulting from trying to convert 99995862 to a date
What is that?