December 1, 2003 at 2:40 am
when i find between in my datetime field it
like
select top 10 * from FACT_INS_BOOKING
where date_key between '2002-10-16' and '2002-10-25'
it doesnot give me 25th oct records but gives till 24th oct
December 1, 2003 at 2:54 am
SQL Server will change the dates into datetime fields so you query will read
select top 10 *
from fact_ins_booking
where date_key between '2002-10-16 00:00:00'
and '2002-10-25 00:00:00'
If your rows have a time stamp then anything on the 25th Oct will not fit the where clause.
One easy solution is to change the query to be less than dateadd(day,1,@enddate):
select top 10 *
from fact_ins_booking
where date_key >= '2002-10-16'
and date_key < '2002-10-26'
Jeremy
December 2, 2003 at 5:28 am
alternatively, just specify the times as well..
select top 10 *
from fact_ins_booking
where date_key between '2002-10-16 00:00:00'
and '2002-10-25 23:59:59'
Edited by - amackay_1 on 12/02/2003 05:28:38 AM
December 2, 2003 at 10:22 am
Might as well add the milliseconds while you are at it.
23:59:59.997
because of time clock precision and rounding, you won't be able to go higher than .997.
Looking forward to a true Date datatype in next version of MSSQL.
December 2, 2003 at 10:27 am
I had the same problem so I use the following:
WHERE Convert(varchar(10),date_key,101) Between @Startdate and @EndDate
@Startdate being '10/16/2002'
@EndDate being '10/25/2002'
Edited by - cfeisel on 12/02/2003 10:29:34 AM
December 2, 2003 at 9:50 pm
I always use Jeremy's method, never have problems.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply