time issue in datetime field

[TravisLaborde]

A big problem I'm having with the datetime field, is that my data comes from differing sources, some of which put in just the date (01/24/2003) and some put in a dateTIME (01/24/2003 10:05:23)...

Because of this, I have no end of problems with things like:

select x from table where date_field = '01/24/2003'

I resort to doing crazy things like:

select x from table where date_field >= '01/24/2003' and date_field < '01/25/2003'

YUCK.

I'm sure there is a better way. Can anyone share with me your own solutions to this problem?

Thanks,
Travis

 

[Zathras]

That's not as crazy as you think. Assuming you have an index on date_field, it is probably the most efficient way to go.

An alternative involves setting up an additional column and a trigger to update it with the pure date during inserts. (And then indexing that column for your queries.) But I don't think it's worth the trouble.

 

[AWithers]

All dates in SQL are really numerical values where the time portion is represented as a decimal part of a day. There are some good FAQs in this forum on the subject

In relation to this you will need to use a query like the one you are using or possibly using the between...and construct

e.g.

select * from youirtable
where youdatefield between '2002/01/01' and '2003/01/01'

Andy

 

[Zathras]

Andy: Wouldn't changing the where clause to

 WHERE date_field between '01/24/2003' and '01/25/2003'

result in picking those rows with date 1/25/03 and no time component as well as any rows with date 1/24/03? I don't think that's what Travis wanted.

 

[ajc123]

1) The BETWEEN solution preserves the index.

2) If this is a ubiquitous problem, you might want to understand the following:

select
getdate(),
cast(getdate() as float),
cast(getdate() as integer)

You can use this technique to eliminate the decimal part of the date.

 

[JamesLean]

Indeed Zathras. Travis would have to use:

WHERE datefield BETWEEN '01/24/2003' AND '01/24/2003 23:59:59.997'

--James

 

[Zathras]

ajc123: Not sure what you mean by preserves the index.

When I look at the execution plan in Query Analyzer, both techniques appear to use the same index in the same way.

Perhaps it can be a problem with some other DBMS, but SQL Server seems to be able to handle it ok.

 

[TravisLaborde]

Wow, what a revelation! I feel good, that my solution isn't as terrible as I thought it was, but.... I'm less than happy with knowing that there isn't a better way.

Thanks much to all of you for letting me know. It's been bothering me, thinking that my way was terribly &quot;non-informed&quot; but at least now I can move on to other issues, knowing that what I'm doing is about all that can be done.

At least maybe I can automate using the BETWEEN clause, and tacking on the 23:59:59 to the date for the ending date... that's better than using the next days date in the < part...

Thanks again!
Travis

 

[tlbroadbent]

Travis,

You may want to read &quot;Handling Dates in SQL Server.&quot; Click the following link. Make sure you check out the other links on the page.

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[ajc123]

Thanks, Zathras, I thought all systems would ignore indexes when functions were applied to them in Where clauses. (Yes, I learned under Oracle, long ago)

Travis can use the CAST statement to do this.

Though I'm sure Terry's article will offer better suggestions. I plan on checking it out myself.


Avery.

 

[Zathras]

You have a valid point ajc123: If a function is used on the left-hand side of the equal sign, then it will probably result in an index scan instead of an index seek. That is probably true with any DBMS.

I have had to use that technique in the past to inhibit the use of the optimizer-selected index in favor of a different index when the optimizer was less than optimum. That was in Gupta SQL Base which doesn't have the syntax to specifiy a particular index the way SQL Server has.