SQLServer 7.0 Query against smalldatetime

Question

SQLServer 7.0 Query against smalldatetime

cindyXavier

SSC Veteran

Points: 281
More actions
July 25, 2003 at 6:23 am

#160285

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

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

SQLBill SSC Guru Points: 51440 More actions · Answer 2

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

cindyXavier SSC Veteran Points: 281 More actions · Answer 3

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

jpipes SSChampion Points: 12230 More actions · Answer 4

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.

cindyXavier SSC Veteran Points: 281 More actions · Answer 5

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

cindyXavier SSC Veteran Points: 281 More actions · Answer 6

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

jpipes SSChampion Points: 12230 More actions · Answer 7

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.

SQLBill SSC Guru Points: 51440 More actions · Answer 8

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

cindyXavier SSC Veteran Points: 281 More actions · Answer 9

SQLBill,

You answered my question. Thanks much

Cindy