Query From SQLCommand to SQLServer DateTime - Fresh mind required pls! 2

[Keyth]

Sorry guys mental block and cant get my head round this one, its only just started happening ?? I think!

I have a table which stores datetime values as one of its fields. I query the table as follows;

qh.Quote_Date = '20090108'
OR
and (qh.Quote_Date between '20090108' and '20090109')

But the incorrect number of rows are returned?

Also, programatical approach ...

Query text:
Select qh.username, count(qi.item_id) as Number_of_Quotes_Entered
from quotes.dbo.QUOTE_HEADER as qh inner join quotes.dbo.quote_item as qi on qh.quote_id = qi.quote_id
Where (qh.Quote_Date between @QuoteDateFrom and @QuoteDateTo)
Group By qh.USERNAME
order by Number_of_Quotes_Entered

SqlCommand.CommandText:
cmd.Parameters.Add("@QuoteDateFrom", SqlDbType.DateTime).Value = DateTimePicker1.Value.Date
cmd.Parameters.Add("@QuoteDateTo", SqlDbType.DateTime).Value = DateTimePicker2.Value.Date

Typical Field Value:
09/01/2009 16:16:32

Any ideas please?
Thanks very much

 

[RiverGuy]

Here, you have an OR immediately followed by an AND, so that won't work:

qh.Quote_Date = '20090108'
OR
and (qh.Quote_Date between '20090108' and '20090109')

If you're simply trying to get everything from one specific date passed in with a 00:00:00.000 time component, regardless of the time, there are several approaches you can take. Here's one:

SELECT * FROM YourTable WHERE DateColumn BETWEEN @DateRequested AND DATEADD(millisecond, -3, @DateRequested)

Or, for a range, it would be

SELECT * FROM YourTable WHERE DateColumn BETWEEN @StartDate AND DATEADD(day, 1, DATEADD(millisecond, -3, @EndDate))

 

[Keyth]

Thanks RiverGuy, the OR was not a statement or query text it was showing examples.

When trying:

SELECT  * FROM         QUOTE_HEADER  WHERE     (QUOTE_DATE = '20090109') ORDER BY QUOTE_ID DESC

No records are returned yet 30 or more are stored in the table?

 

[gmmastros]

Typical Field Value:
09/01/2009 16:16:32

But... your query has...

[tt][blue]QUOTE_DATE = '20090109'[/blue][/tt]

since your query doesn't involve a time component, it's assuming midnight. Instead, you should search on a range, like this...

SELECT  * 
FROM    QUOTE_HEADER  
WHERE   QUOTE_DATE >= '20090109'
        And QUOTE_DATE < '20090110'
ORDER BY QUOTE_ID DESC

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

The SQL statement you have displayed will only return records which have a QUOTE_DATE of exactly Janaury, 9, 2009 at midnight. As shown above, you need to account for records which have times other than midnight. There are several ways to do this. This for example:

SELECT  * FROM         QUOTE_HEADER  WHERE     (QUOTE_DATE >= '20090109' AND QUOTE_DATE < '20090110') ORDER BY QUOTE_ID DESC

 

[Keyth]

Ah right.

So for dates between 08-01-2009 and 09-01-2009 I would query as follows:

Select qh.username, count(qi.item_id) as Number_of_Quotes_Entered
from quotes.dbo.QUOTE_HEADER as qh inner join quotes.dbo.quote_item as qi on qh.quote_id = qi.quote_id
Where (qh.Quote_Date between @QuoteDateFrom and @QuoteDateTo)
Group By qh.USERNAME
order by Number_of_Quotes_Entered

SqlCommand.CommandText:
cmd.Parameters.Add("@QuoteDateFrom", SqlDbType.DateTime).Value = DateTimePicker1.Value.Date
cmd.Parameters.Add("@QuoteDateTo", SqlDbType.DateTime).Value = DateTimePicker2.Value.Date.AddDays(1)

Thanks again

 

[gmmastros]

Almost.

Suppose you wanted to get all the rows from your table for August 2008. Your query could potentially return some rows in September, but only if the time is midnight. The happens because BETWEEN is inclusive (it returns value that are equal to the range, not just between).

Between '20080801' and '20080901' will return rows from midnight on Aug 1, 2008 to midnight Sept 1, 2008.

Instead of using between (with dates), it's better to have 2 where clause conditions, like this:

Select qh.username, count(qi.item_id) as Number_of_Quotes_Entered 
from quotes.dbo.QUOTE_HEADER as qh inner join quotes.dbo.quote_item as qi on qh.quote_id = qi.quote_id 
Where qh.Quote_Date >= @QuoteDateFrom 
      qh.Quote_Date < and @QuoteDateTo
Group By qh.USERNAME 
order by Number_of_Quotes_Entered

Some people mistakenly think that multiple where clause conditions will be slower to execute than a single BETWEEN condition, but this is simply not true. Performance will be the same, but your data will be more accurate.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom