Date-specific query won't work 3

[jasonsalas]

I'm TRYING to write a SPROC that will return a date-specific recordset for a collection of articles in a single DB table. For some stupid reason, when I try querying the table for a specific date it doesn't work:

SELECT * FROM table WHERE Date = '7/12/2004'

I know I have records matching the query criteria, and it works fine if I do other queries not for a precise date, like:

SELECT * FROM table WHERE Date >= '7/12/2004'

My DATE field is of type SMALLDATETIME.

Huh?

 

[jasonsalas]

Oddly, this works for getting a day's stories:

DECLARE @QueryDate SMALLDATETIME
SET @QueryDate = '7/15/2004'

SELECT TOP 20 Date FROM table
WHERE Date BETWEEN @QueryDate AND DATEADD(day,1,@QueryDate)

I've never had to do date-specific queries for a single date (I've always worked with ranges) so this is as new to me as it is frustrating.

 

[amorous]

Date is a reserved word...

Try this:

SELECT * FROM table WHERE [Date] = '7/12/2004'

and also

SELECT * FROM table WHERE CONVERT(Varchar(10),[Date],103) = '7/12/2004'

-VJ

 

[dbomrrsm]

Try

SELECT * FROM table WHERE CONVERT(varchar, Date, 101) = CONVERT(Varchar, '7/12/2004',101)

DBomrrsm

 

[jasonsalas]

Thanks guys...but it's still weird - none of the other SQL statements are returning any records. Only my goofy BETWEEN construct seems to work.

 

[SQLBill]

Here's the problem....DATETIME and SMALLDATETIME don't store just the date. This is not a big problem when you use BETWEEN or > and <. However, when you look for an exact match (=) it is a problem.

7/14/2004 is not equal to 7/14/2004 12:00:00 PM

and SQL Server stores DATETIME/SMALLDATETIME as both date AND time.

CONVERT your SMALLDATETIME column to a format that is only the date, for example:

 WHERE CONVERT(VARCHAR(10),[Date],101) = '7/14/2004'

-SQLBill

 

[Krickles]

It seems your smalldatetime field must contain time entries other than midnight (00:00:00). If so, your best bet is to use the BETWEEN clause:

WHERE [date] BETWEEN '2004-07-15 00:00:00' AND '2004-07-15 23:59:59'

Krickles | 1.6180

 

[jdgonzalez]

You could try something like this:

where convert(char(10), [date], 101) = '07/16/2004'

Do you dates also contain a time component other than midnight?

 

[SQLSister]

Between would probably be more efficient than using convert.

Questions about posting. See faq183-874

 

[ESquared]

Between works as long as you use the last valid value for the datatype

smalldatetime - 23:59:59
datetime - 23:59:59.997

Otherwise just use < the next day at 12 am

 

[jasonsalas]

Thanks all for your input. I've gotten some insight on what turned out to be a pretty strange problem. Here's what I found works, returning both the same resultset:

DECLARE @QueryDate SMALLDATETIME
SET @QueryDate = '7/7/2004'

SELECT ArticleID,Title,Date FROM News8LeadStories WHERE Date BETWEEN @QueryDate AND DATEADD(day,1,@QueryDate) ORDER BY ArticleID DESC

SELECT ArticleID,Title,Date FROM News8LeadStories WHERE Date BETWEEN @QueryDate + ' 00:00:00' AND @QueryDate + ' 23:59:59' ORDER BY ArticleID DESC
GO

 

[ESquared]

Hi jasonsalas,

• Your first select statement will also return records from 7/8/2004 12 am because BETWEEN is inclusive. In any case you can just do @QueryDate + 1, no need to use DateAdd.

• In the second select, it is unnecessary to add +'00:00:00' because that is truly adding zero.

• After refreshing my memory in BOL, I found that smalldatetime does not store seconds. So, adding '23:59:59' ends up adding 24 hours and thus it is identical to the previous select. Change it to '23:59' for correct selection.

• I see that you have a leading space character in your time additions. This space character is unnecessary. The query engine is not adding a string to a string, it is adding a date to a date. Because of the way dates are stored internally in SQL server, '23:59' is equivalent to '1900-01-01 23:59', (and 23:59:59 is 24 hours) so you're really doing:

[tt]'1900-01-02 00:00:00' + @QueryDate[/tt]

• For clarity, ease of maintenance, and to help out anyone else who later needs to maintain your code, I suggest the following syntax:

SELECT ArticleID, Title, [Date]
   FROM News8LeadStories
   WHERE
      [Date] >= @QueryDate
      AND [Date] < @QueryDate + 1
   ORDER BY ArticleID DESC

Note the second comparison is not <=.

As someone else mentioned, Date is a reserved word. You may avoid future problems if you change it to a non-reserved word such as StoryDate.

 

[jasonsalas]

Thanks ESquared! I'll keep that in mind.

 

[jasonsalas]

someone suggested the following offline:

SELECT * FROM Orders Where CAST(CONVERT(varchar,OrderDate,101) AS DateTime) = '7/7/2004'

Convert the column to 'mm/dd/yy' (101) style and CAST it back to a datetime.

 

[ESquared]

jasonsala,

I recommend against tjat method because the conversion must be done for every row in the entire table and to boot may not use any indexes. Selecting with the >= AND < method has neither of these problems.

 

[MANETUK]

many many thanks for this tidy solution

new to sql server and datetime storage

you saved me much time