July 25, 2003 at 6:23 am
If I have a table tblProjects that has a smalldatetime field called insertedDate, what is the correct query to pull all records between 01/01/03 AND 04/30/03?
Is using BETWEEN AND or >= AND <= more efficient or simpler?
This is my query:-
SELECT * FROM tblprojects WHERE insertedDate >= CONVERT(smalldatetime, '01/01/2003', 101) AND insertedDate <= CONVERT(smalldatetime, '04/30/2003',101)
July 28, 2003 at 8:00 am
This was removed by the editor as SPAM
July 28, 2003 at 11:21 am
Cindy,
First, this is the forum for the Question of the Day. The QOD is a 'test' question that appears in the SQLSERVERCENTRAL newsletter (it can also be found by going to the Test Center or Resources drop down menu.)
You should have posted in the T-SQL or Programming forum.
Now to answer your question: BETWEEN will work fine. But not the way you are using it. I expect you want everything from 01/01/03 00:00:00 to 04/30/03 23:59:59, correct?
I suggest using unambiguous dates (01/01/03 can be interpreted to be 1 January 2003 or January 1 2003. What's the problem with that? Well if it's being interpreted as dd/mm/yy - what is 04/30/03????)
So, I suggest the following:
WHERE insertedDate BETWEEN CONVERT(smalldatetime, '01/01/2003', 101)
AND insertedDate <= CONVERT(smalldatetime, '05/01/2003',101)
-SQLBill
July 28, 2003 at 11:27 am
I missed answering your real question...both will work just fine as long as you are giving the correct data. When the CONVERT happens, your query is really:
SELECT * FROM tblprojects WHERE insertedDate >= '01/01/03 00:00:00' AND insertedDate <= '04/30/03 00:00:00'
SMALLDATETIME and DATETIME are ALWAYS a date AND time. So your query (whether you use BETWEEN or >= AND <=) will not return data from 04/30/03.
-SQLBill
July 28, 2003 at 11:33 am
SQLBill,
First for some reason I could not find my way to post in T-SQL or programming forums.
The dates are interpreted as 'mm/dd/yy' and not 'dd/mm/yy'.
<= '05/01/2003' will include '05/01/03'
So I will try using:-
WHERE insertedDate BETWEEN CONVERT(smalldatetime, '01/01/2003', 101)
AND insertedDate < CONVERT(smalldatetime, '05/01/2003',101)
Thank You
Cindy
July 28, 2003 at 11:36 am
Or, don't bother with any of the conversion stuff and just:
CREATE PROC dbo.GetProjects
@StartDate SMALLDATETIME
, @EndDate SMALLDATETIME
AS
BEGIN
SELECT Fields -- Don't use * !!!
FROM tblProjects
WHERE insertedDate
BETWEEN DATEDIFF(day, 0, @StartDate)
AND DATEDIFF(day, 0, @EndDate)
END
The DATEDIFF strips the time portion of the smalldatetime/datetime field. Since DATETIME fields are stored as integers internally, the BETWEEN can operate efficiently by comparing numbers to numbers.
July 28, 2003 at 11:40 am
SQLBill,
The convert function will truncate the time part and give the output in the format 'mm/dd/yy' (for 101).
Then why wouldn't the query
SELECT * FROM tblprojects WHERE insertedDate >= CONVERT(smalldatetime, '01/01/2003', 101) AND insertedDate <= CONVERT(smalldatetime, '04/30/2003',101)
include '04/30/03'??
Cindy
July 28, 2003 at 11:53 am
jpipes,
My actual query has other combinations in the where clause . This stored proc will come in handy another time.Thank You.
The original question was intended to be:-
why does the query not return data from 04/30/03 after the time part was truncated with the convert function. I guess I have to use
WHERE insertedDate >= CONVERT(smalldatetime, '01/01/2003', 101)
AND insertedDate < CONVERT(smalldatetime, '05/01/2003',101)
OR use the Between AND clause.
Thanks much
Cindy
July 28, 2003 at 11:57 am
quote:
jpipes,My actual query has other combinations in the where clause . This stored proc will come in handy another time.Thank You.
The original question was intended to be:-
why does the query not return data from 04/30/03 after the time part was truncated with the convert function. I guess I have to use
WHERE insertedDate >= CONVERT(smalldatetime, '01/01/2003', 101)
AND insertedDate < CONVERT(smalldatetime, '05/01/2003',101)
OR use the Between AND clause.
Cindy, the reason it's truncating is because CONVERT(param, param, 101) expects to be conveting to a CHARACTER datatype. The 101 us ignored in your script because you are converting FROM a string TO a DATETIME. Please see Books On Line for more info.
July 28, 2003 at 12:11 pm
Cindy,
"The convert function will truncate the time part and give the output in the format 'mm/dd/yy' (for 101)"
Only works when you are converting to a CHAR or VARCHAR format. Converting to SMALLDATETIME or DATETIME just adds the default time of 00:00:00 back on.
So effectively you are taking this:
04/30/03 05:00:00
Truncating it to:
04/30/03
And then converting it to SMALLDATETIME:
04/30/03 00:00:00
-SQLBill
July 28, 2003 at 12:13 pm
SQLBill,
You answered my question. Thanks much
Cindy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply