Date Logic

[earthandfire]

I'd like to confirm (or otherwise) my understanding of dates:

Assuming a field called MyDate (datatype datetime)

(UK date format)

1: MyDate >=CONVERT(datetime,'01/05/2005',103) will include ALL records for 1st May 2005 and subsequent dates

2: MyDate <=CONVERT(datetime,'31/05/2005',103) might miss some records for 31st May 2005

3: MyDate BETWEEN CONVERT(datetime,'01/05/2005',103) AND CONVERT(datetime,'31/05/2005',103) might not contain every record for May 2005

4: MyDate >=CONVERT(datetime,'01/05/2005',103) AND MyDate <CONVERT(datetime,'01/06/2005',103) guarantees every record for May 2005.

I've hard-coded the dates here, they will be in fact be variables.

The reason that 2 & 3 may miss some dates is to do with the time element (in which I have no interest - but can't guarantee for example that it will 00:00)?

Thanks in advance.

 

[vongrunt]

Yesyesyes... and yes.

Method #4 always works and still gives optimizer the chance to use index on MyDate column when available.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[earthandfire]

Thanks for the confirmation.

 

[SQLBill]

What this:
2: MyDate <=CONVERT(datetime,'31/05/2005',103)
really says is:

2: MyDate <=CONVERT(datetime,'31/05/2005 00:00:00.000',103)

Which is why you will miss some values from the 31st.

And this:
3: MyDate BETWEEN CONVERT(datetime,'01/05/2005',103) AND CONVERT(datetime,'31/05/2005',103) might not contain every record for May 2005

Really means this:
3: MyDate BETWEEN CONVERT(datetime,'01/05/2005 00:00:00.000',103) AND CONVERT(datetime,'31/05/2005 00:00:00.000',103)

Which is why it again may miss values from the 31st.

-SQLBill

Posting advice: FAQ481-4875

 

[earthandfire]

SQLBill thanks, that's what I thought. I just didn't want to do things 'the hard way', if I didn't have to.