Formatting a date field in a stored procedure

[levinll]

I'm trying to compare a date range against a date/time field in one of my tables. The field contains both a date and a time (ie 10/06/2002 8:25pm). However I only want to compare against the date portion of the field. Is there a way to format the field in a stored procedure so I only compare the date portion against parameters I pass to the procedure ?

Thanks !!!

 

[aalmeida]

yes try to use the cast() or convert() functions to store it into a variable, becarefull with the date format you choose since the cast functio will say that 1/01/2001 is not a date and give you an error like overflow or date out of range. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[foxdev]

You might also consider using DateDiff, as in dates are different if:

DATEDIFF ( dd , date1 , date2 ) > 0
--------------

http://www.lowcostprints.com

 

[levinll]

Perhaps seeing my stored procedure will give you a better idea of what I'm trying to accomplish

CREATE PROCEDURE [GetEditedProcessedTimecards]
@bdate datetime,
@edate datetime
AS
select * from GetEditedProcessedRecords where EditDate between @bdate and @edate

EditDate is a datetime field in a table that both date and time information in it (ie 10/07/2002 8:30pm). The two parameters I'm passing to this procedure only have a date in them. I only want to compare the date part of the EditDate field, ignoring the time portion of it.

 

[tlbroadbent]

Here are two solutions.

1st query:
select *
from GetEditedProcessedRecords
where convert(datetime, convert(int, EditDate))
between @bdate and @edate

2nd query:
Set @ed = @edate + Convert(datetime, '23:59:59')

select *
from GetEditedProcessedRecords
where EditDate
between @bdate and @edate

I prefer the 2nd query because SQL only has to perform the converion and addition once. With the first query the conversion occurs for every record on the table. In addition, If there is an index, the 2nd query will use while the first will not.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tlbroadbent]

Correction! 2nd query set statement should read:

Set @edate = @edate + Convert(datetime, '23:59:59')
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.