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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy