We help IT Professionals succeed at work.

how to get date range in the stored procedure

SayYou_SayMe
SayYou_SayMe asked
on
the buiness requirement is that
pass the date range from .net to the stored procedure in Sybase to select these date range records

for example , product table

id  product_name  date
1   monitor           01/29/2010
2   driver              12/01/2008

created a stored procedure :
create procedure search_product
@date_from datetime,
@date_to datetime
as
begin
select product_name, date from product
where convert(char(10),date,101)>convert(char(10),@date_from,101)
and convert(char(10),date,101)<convert(char(10),@date_to,101)

but it does not work , can not get the expected result
any idea?

thanks
Comment
Watch Question

this is just and idea that try to convert both values in DateTime and then compare in where clause,
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012
Commented:
What happens when you don't use the converts?

By the way, the convert formats for dates (the "101"s you're using) only affect how a Sybase ASE date/time/datetime is displayed, it won't affect internal calculations.
i use the query as
select * from TehBazari_payments TP
 inner join TehBazari T on T.recordid=tp.tehbazariID " +
 WHERE convert(datetime,convert(varchar(20),paymentDate,103),103) between
 convert(datetime,convert(varchar(20),'" + dtStartDate.ToString("dd-MM-yyyy") + "',103),103) AND
 convert(datetime,convert(varchar(20),'" + dtEndDate.ToString("dd-MM-yyyy") + "',103),103)
 

Hope this will give u a idea
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Agree with Joe

Why are you converting to CHAR?  You end up with strings like '01/02/2010' and '03/04/2009'
In text form, '03/04/2009' > '01/02/2010' so you have lost all date comparison context.

This should work, even if the "date" column contains time information

where date>=@date_from and date<@date_to+1
Kaushal AroraTechnical Analyst
Commented:
If you are to send the date and time together as parameters then try this one.

create procedure search_product
@date_from varchar(30),
@date_to varchar(30)
as
begin
select product_name, date from product
where convert(datetime,date,103)>convert(datetime,@date_from,103)
and convert(datetime,date,103)<convert(datetime,@date_to,103)

If you only want to send the date i.e 13/06/2010 (in dd/MM/yyyy format) then use


create procedure search_product
@date_from varchar(30),
@date_to varchar(30)
as
begin
select product_name, date from product
where convert(datetime,convert(varchar(10),date,103),103)>convert(datetime,convert(varchar(10),@date_from,103),103)
and convert(datetime,convert(varchar(10),date,103),103)<convert(datetime,convert(varchar(10),@date_to,103),103)

remember you need to send the parameter as varchar not as datetime. Also 103 format is for dd/MM/yyyy, if you want to send the datetime in MM/dd/yyyy format then use 101 in place of 103.

Hope this helps you.
I think this is what you need:

CREATE PROCEDURE search_product
@date_from DATETIME,
@date_to DATETIME
AS
BEGIN
      SELECT
            product_name, date
      FROM
            product
      WHERE
            date BETWEEN CONVERT(DATETIME,CONVERT(VARCHAR(32),@date_from,101)) AND CONVERT(DATETIME,CONVERT(VARCHAR(32),@date_to,101))
END


Enjoy,
Ashish Mehta

Author

Commented:
thanks guys

it gives me error message: incoorect syntax near the keyword 'end'
create procedure search_product
@date_from varchar(10),
@date_to varchar(10)
as
begin
select product_name, date from product
where ((convert(datetime,convert(varchar(10),date,101),101)>=isnull(convert(datetime,convert(varchar(10),@date_from,101),101),convert(datetime,convert(varchar(10),date,101),101)) or date is null )
and ((convert(datetime,convert(varchar(10),date,101),101)<=isnull(convert(datetime,convert(varchar(10),@date_to,101),101),convert(datetime,convert(varchar(10),date,101),101)) or date is null )
end

because use maybe does not pass the date ,and also the date in the database maybe is null

could you please help me out?
thanks again

There was issue with brackets

Create procedure search_product
(
@date_from varchar(10),
@date_to varchar(10)
)
as
begin
select product_name, date from product
where ((convert(datetime,convert(varchar(10),date,101),101)>=
isnull(convert(datetime,convert(varchar(10),@date_from,101),101),convert(datetime,convert(varchar(10),date,101),101)) or date is null))
and (convert(datetime,convert(varchar(10),date,101),101)<=
isnull(convert(datetime,convert(varchar(10),@date_to,101),101),convert(datetime,convert(varchar(10),date,101),101)) or date is null )
end

Author

Commented:
thanks

but the error message is the same,

it is not correct of the syntax of

((convert(datetime,convert(varchar(10),date,101),101)>=
isnull(convert(datetime,convert(varchar(10),@date_from,101),101),convert(datetime,convert(varchar(10),date,101),101)) or date is null))

because when not to use this convert function , no error message

could any body fix it out ?

thanks
 
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Hi SayYou_SayMe

Is there a particular reason for persisting with convert?
When using date parameters and date fields, there is no need to use convert at all.
It also makes SQL Server unable to use any indexes on the date column when you CONVERT it.
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012
Commented:
Exactly. If the columns are defined as a datetime and the stored procedure parameters are too, you don't need any converts at all.
Hi, it seems like you want to compare just date regardless of time. see th attached code. It will give you all records starting at @date_from 0:00 until @date_to 23:59:59.

create procedure search_product
@date_from datetime,
@date_to datetime
as
begin
-- strip time from @date_from
select @date_from = convert(datetime( convert(char(10),@date_from,101))
-- strip time from @date_to
select @date_to = convert(datetime( convert(char(10),@date_to,101))
-- add 1 day to @date_to, because we need all the day
select @date_to = dateadd( day, 1, @date_to )

select product_name, date from product
where date >= @date_from
and date < @date_to

Open in new window