Comparing Dates

[Sanjay]

How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-

6/6/2001 7:23:42 PM
6/6/2001 8:01:08 PM and so on....

I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.

Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.

Thanks
Sanjay.

[Sanjay]

Sorry i forgot to mention in the previous post. Within the SP i am using the BETWEEN clause for comparing dates. Like :-

Where [Date] Between @DateFrom And @Dateto.

Sanjay.

------------
Sanjay at 7/10/01 2:34:37 PM

How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-

6/6/2001 7:23:42 PM
6/6/2001 8:01:08 PM and so on....

I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.

Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.

Thanks
Sanjay.

[Patrick]

You could look at and use 'convert or cast'
these instructions give a subset of the full stored datetime.
Choose the 'style' your application needs.



------------
Sanjay at 7/10/01 2:36:58 PM

Sorry i forgot to mention in the previous post. Within the SP i am using the BETWEEN clause for comparing dates. Like :-

Where [Date] Between @DateFrom And @Dateto.

Sanjay.

------------
Sanjay at 7/10/01 2:34:37 PM

How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-

6/6/2001 7:23:42 PM
6/6/2001 8:01:08 PM and so on....

I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.

Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.

Thanks
Sanjay.

[Paul]

Hi, Sanjay -

Try:
"WHERE convert(datetime, convert(char, [date], 101)) Between @DateFrom and @DateTo"

The convert to character (with style 101) removes the time portion and then converting back to datetime makes it 12:00AM - just like the parameters being passed in.

Hope this helps.
Paul


------------
Sanjay at 7/10/01 2:36:58 PM

Sorry i forgot to mention in the previous post. Within the SP i am using the BETWEEN clause for comparing dates. Like :-

Where [Date] Between @DateFrom And @Dateto.

Sanjay.

------------
Sanjay at 7/10/01 2:34:37 PM

How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-

6/6/2001 7:23:42 PM
6/6/2001 8:01:08 PM and so on....

I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.

Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.

Thanks
Sanjay.

Sanjay,

I think that the most efficient way to correct your problem is to add one day to the @DateTo parameter. Therefore, if the user selects 6/6/01 to 6/6/01, you will be searching for 6/6/01 12am to 6/7/01 12AM. If the db might have entries for 6/7/01 12am, you can even subtract a minute.

Try the following:

declare @DateFrom as smalldatetime
declare @DateTo as smalldatetime

set @DateFrom = '06/06/01'
set @DateTo = '06/06/01'
set @DateTo = dateadd(day,1,@DateTo)
--set @dateto = dateadd(mi,-1,@DateTo) --if you want to be very precise

select @DateTo, *
from
table1
where datecolumn between @DateFrom and @Dateto

Tom

------------
Sanjay at 7/10/01 2:34:37 PM

How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-

6/6/2001 7:23:42 PM
6/6/2001 8:01:08 PM and so on....

I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.

Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.

Thanks
Sanjay.

[Sanjay]

Thanks Paul. It worked.

------------
Paul at 7/10/01 2:44:54 PM

Hi, Sanjay -

Try:
"WHERE convert(datetime, convert(char, [date], 101)) Between @DateFrom and @DateTo"

The convert to character (with style 101) removes the time portion and then converting back to datetime makes it 12:00AM - just like the parameters being passed in.

Hope this helps.
Paul


------------
Sanjay at 7/10/01 2:36:58 PM

Sorry i forgot to mention in the previous post. Within the SP i am using the BETWEEN clause for comparing dates. Like :-

Where [Date] Between @DateFrom And @Dateto.

Sanjay.

------------
Sanjay at 7/10/01 2:34:37 PM

How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-

6/6/2001 7:23:42 PM
6/6/2001 8:01:08 PM and so on....

I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.

Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.

Thanks
Sanjay.