May 3, 2006 at 7:00 am
I am using the query below to generate results for an ASP page. The query works for the most part, but for some reason, the dates are not working as they should.
In the query below, PayDay is DEenteredDate. For the script, I place variables in the date range fields, but for the example below, I just put 5/1/2006 to 5/2/2006.
The problem is, the BETWEEN statement is not being inclusive as it should. If there are items with a DEenteredDate of 5/1/2006, the query will not return them if I put:
BETWEEN '5/1/2006' AND '5/1/2006'
I have to put BETWEEN '5/1/2006' AND '5/2/2006' to get any results with a DEenteredDate of 5/1/2006.
I tried using PayDay >= '5/1/2006' AND PayDay <= '5/1/2006' but that doesn't work either.
Can anyone see what might be the problem?
SELECT DISTINCT(WRid) AS JobNum,
WRenteredDate, PayDay, WRcurrentStatus, WRclaimNumber,
DEaltKeyValue, DEenteredDate, BMcopynetInvoiceNumber
FROM
workrequest
LEFT OUTER JOIN billmaster ON WRid = BMworkrequestId,
(SELECT DISTINCT(DEkeyValue) AS JobNumber, DEenteredDate AS PayDay
FROM DiaryEntry WHERE 1=1
AND DEentryTypeID='ENRPAY'
GROUP BY DEkeyValue, DEenteredDate) AS cnt, diaryentry
WHERE JobNumber=WRid AND PayDay BETWEEN '5/1/2006' AND '5/2/2006'
AND DEkeyValue=WRid AND diaryentry.DEentryTypeID='ENRINV'
AND DEdeletedDate IS NULL AND WRjobTypeID <>'TRJTINT'
GROUP BY WRid, WRenteredDate, PayDay, WRcurrentStatus, WRclaimNumber, DEaltKeyValue, DEenteredDate, BMcopynetInvoiceNumber
ORDER BY DEenteredDate
May 3, 2006 at 7:10 am
The problem is that when you don't include a time, SQL Server uses the default of 00:00:00.000. So, what is BETWEEN '5/1/2006 00:00:00.000' AND '5/1/2006 00:00:00.000'?
Something else to consider: is 5/1/2006 May 1, 2006 or is it 5 January 2006? How is SQL Server to know? Either use 2006-05-01, or start your script with SET DATEFORMAT MDY.
-SQLBill
May 3, 2006 at 8:04 am
SQLBill,
Thank you for your suggestions. I tried using:
BETWEEN '2006-05-01 00:00:00.000' AND '2006-05-01 00:00:00.000'
But still no results were returned. I am not sure what the heck is going on here.
May 3, 2006 at 8:14 am
You wouldn't get any results, unless you had an entry for exactly midnight. To put it in terms of simple math, that's the same as asking for values between 1 and 1.
The statement should be:
BETWEEN '2006-05-01 00:00:00.000' AND '2006-05-02 00:00:00.000'
or better ANSI SQL:
>= '2006-05-01 00:00:00.000'
AND
< '2006-05-02 00:00:00.000'
May 3, 2006 at 8:17 am
Pam,
That does work, but shouldn't BETWEEN '2006-05-01 00:00:00.000' AND '2006-05-01 00:00:00.000' find all results with a PayDay(DEenteredDate) of 5/1/2006?
Thanks!
May 3, 2006 at 8:21 am
"but shouldn't BETWEEN '2006-05-01 00:00:00.000' AND '2006-05-01 00:00:00.000' find all results with a PayDay(DEenteredDate) of 5/1/2006?"
Yes. If and only if the DEenteredDate does not contain time or time is 00:00:00
May 3, 2006 at 8:24 am
if you are passing the date (without time) in as a paremeter you can do this
where PayDay >= @datevar
and PayDay < dateadd(day, 1, @datevar)
May 3, 2006 at 8:25 am
I just checked, and DEenteredDate does have time, like this:
2006-03-15 11:13:57.000
so should my search be BETWEEN '2006-05-01 00:00:00.000' AND '2006-05-01 11:59:59.000' ?
May 3, 2006 at 8:28 am
"so should my search be BETWEEN '2006-05-01 00:00:00.000' AND '2006-05-01 11:59:59.000' ?"
Yes.
But it would be easier to specify using
PayDay >= '2006-05-01'
and PayDay < '2006-05-02
or use the method that i posted
May 3, 2006 at 8:32 am
Well not quite...
BETWEEN '2006-05-01 00:00:00.000' AND '2006-05-01 11:59:59.000'
would only get you until a minute before lunch (depending on your date format and work schedule, that is...).
May 3, 2006 at 8:41 am
PS
I posted that previous reply before noticing you're having trouble getting your script to work properly. I know the between works on dates perfectly because I use it every day i the format I just mentioned. I have seen it fail in te manner you mention several times but it was for a reason that was so painfully obvious that I wonder if I dare mention it.
Are your fields of type datetime or varchar? If they are of varchar then the standard method of expressing a date surrounded by single quotes is going to fail you miserably.
May 3, 2006 at 8:41 am
PS
I posted that previous reply before noticing you're having trouble getting your script to work properly. I know the between works on dates perfectly because I use it every day i the format I just mentioned. I have seen it fail in te manner you mention several times but it was for a reason that was so painfully obvious that I wonder if I dare mention it.
Are your fields of type datetime or varchar? If they are of varchar then the standard method of expressing a date surrounded by single quotes is going to faile you miserably.
May 3, 2006 at 8:49 am
Awesome...both ways work. I tried KH's way and Pam's way of using:
BETWEEN '2006-05-01 00:00:00.000' AND '2006-05-01 23:59:59.000'
Thanks Guys!
May 4, 2006 at 12:39 am
You will still need to tweak your time, re:
SQL Server datetime datatype allows for a date value from 1/1/1753 through 12/31/9999 and a time accuracy to one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). So a time value of '23:59:59.999' is rounded to '00:00:00.000', so the closest you can get to before mid-night is '23:59:59.998' and after mid-night is '00:00:00.002', otherwise '23:59:59.999' thru '00:00:00.001' = ''00:00:00.000' which IS NULL.
SQL Server smalldatetime datatype allows for a value from 1/1/1900 through 6/6/2079 and a time accuracy to the minute. So a time with a value of '12:35:29.998' is rounded to '23:35' while '12:35:29.999' is rounded to '23:36'
Andy
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply