quote:

---

Originally posted by SwePeso

Your REAL problem is that someone convinced you to use varchar datatype to store datetime information.

---

N 56°04'39.26"
E 12°55'05.63"

---

quote:

---

Originally posted by russell

WHERE	LEFT(dt, 4) = DatePart(year, getdate())
And	SUBSTRING(dt, 5, 2) = DatePart(month, getdate())

---

set nocount on

create table #sample
	(
		id int identity(1, 1) primary key clustered,
		data varchar(20),
		dt as convert(date, left(data, 8), 112) persisted
	)

-- populate 100k sample rows
insert #sample 
select top(1000000) replace(replace(replace(convert(varchar(16), dateadd(minute, checksum(newid()) / 10, '24000101'), 120), ' ', ''), ':', ''), '-', '')
from master..spt_values as v1 
inner join master..spt_values as v2 on v2.type = 'P'
where v1.type = 'p'

create nonclustered index ix_dt on #sample (dt) include(data)

declare @now date = (select dt from #sample where id = 500000)

SET STATISTICS IO ON
SET STATISTICS TIME ON

-- Original
print '-- This is the original query'
select data from #sample where LEFT(data, 4) = DatePart(year,@now) And SUBSTRING(data, 5, 2) = DatePart(month, @now)

-- Peso
print '-- This is Peso query'
select data from #sample where dt >= dateadd(month, datediff(month, 0, @now), 0)
and dt < dateadd(month, datediff(month, -1,@now), 0)

SET STATISTICS TIME OFF
SET STATISTICS IO OFF
 
drop table #sample

/*
-- This is the original query
Table 'Worktable'. Scan count 1, logical reads 3600, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 181 ms.


-- This is Peso query
Table 'Worktable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
*/