date from date time sql server 2005

[pungigis]

Need help with the following, fairly new to sql.

The inner join is not working correctly because the transactiondate has an actual time and the fulldate has all zeros so I just want to join on the dates, how do I strip off the time and accomplish this????


select
r.startcall,
r.skillset,
r.transactiondate,
r.calltime,
d.fulldate,
d.dayofweek
from
dbo.vw_reportinginboundcalldata r
inner join dbo.dim_date d on r.transactiondate = d.fulldate
where
r.startcall between '08/01/2010' and '08/07/2010' and
r.skillset = 'GRAYS' and
r.calltime not like 'After%' and
d.dayofweek in ('2','3','4','5','6')

 

[markros]

Don't use between with datetime values. Take a look at these two very comprehensive blogs explaining the issue in details:

The ultimate guide to the datetime datatypes
Bad habits to kick : mis-handling date / range queries


PluralSight Learning Library

 

[pungigis]

I appreciate the blogs but they are not really showing me how to strip the date out of the date time, I understand why I shouldn't use between but still need to be able to strip the date out, if someone can show me an example that would be great.

 

[markros]

I think Tibor's blog showed this.

Anyway, to strip time info
<pre>
declare @d datetime
set @d = getdate()
select @d as WithTime, dateadd(day, datediff(day,'19000101', @d), '19000101') as WithoutTime</pre>

PluralSight Learning Library

 

[markros]

Tibor's blog discussing this issue:

http://www.karaszi.com/SQLServer/info_datetime.asp#GettingRidOfTimePortion

PluralSight Learning Library

 

[markros]

Also, in SQL Server 2008 you can just cast datetime value to date. Interestingly, in quick tests we recently tried looks like casting datetime to date is quicker than other options

(See

http://blogs.lessthandot.com/index....erver/how-to-return-only-weekend-data-for-the

blog - 2008 is 40% vs. 60% for SQL 2000 query - although few records tables are not representative enough).

PluralSight Learning Library