Like query on Date Time

Question

Hi friends,is this possible to fire like query on DateTime in SQL Server 2005.

select cur.EvrIdent,
cur.VrdTarih,
cur.VrdModTim,
cur.VrdNo,
(select PmpKod from Bnz_Pompalar where PmpIdent=(select GunPmpId from Bnz_Tabancalar where GunIdent=TbdGunId)) as [Pump No],
(select GunKod from Bnz_Tabancalar where GunIdent=TbdGunId) as [Nozzle No],
(select StokKod from Stoklar where StokIdent=(select GunStkId from Bnz_Tabancalar where GunIdent=TbdGunId)) as [Fuel Type],
isnull((select tprv.TbdSon from Bnz_TabancaDetay tprv where tprv.TbdEvrIdent=(select top 1 prv.EvrIdent from Bnz_Vardiya prv where prv.EvrIdent < cur.EvrIdent order by prv.EvrIdent desc) and tprv.TbdGunId=t.TbdGunId),0) as [Accumulated Opening],
t.TbdSon as [Accumulated Final],
isnull((select tprv.TbdTotal from Bnz_TabancaDetay tprv where tprv.TbdEvrIdent=(select top 1 prv.EvrIdent from Bnz_Vardiya prv where prv.EvrIdent<cur.EvrIdent order by prv.EvrIdent desc) and tprv.TbdGunId=t.TbdGunId),0) as [Electronic Tot Opening],
t.TbdTotal as [Electronic Tot Final] 
from Bnz_TabancaDetay t 
join Bnz_Vardiya cur on (EvrIdent=TbdEvrIdent) 
--where cur.VrdModTim='" + date + "' 
order by cur.VrdTarih,cur.VrdNo,[Pump No],[Nozzle No]

accordin to this query i get the record like this

1   2010-03-09 00:00:00.000 2010-03-11 05:57:31.247 1   01              101             TRB                     .0000   37.5600 .0000   428110.7700
1   2010-03-09 00:00:00.000 2010-03-11 05:57:31.247 1   01              201             HS                      .0000   332.5700    .0000   3708985.9100
1   2010-03-09 00:00:00.000 2010-03-11 05:57:31.247 1   02              102             TRB                     .0000   .0000   .0000   275481.1400
1   2010-03-09 00:00:00.000 2010-03-11 05:57:31.247 1   02              202             HS                      .0000   1041.9500   .0000   3496286.9500
1   2010-03-09 00:00:00.000 2010-03-11 05:57:31.247 1   03              103             UL                      .0000   .0000   .0000   346131.1600
2   2010-03-11 00:00:00.000 2010-03-12 05:59:02.937 1   01              201             HS                      332.5700    457.4500    3708985.9100    3710130.6100
2   2010-03-11 00:00:00.000 2010-03-12 05:59:02.937 1   02              102             TRB                     .0000   .0000   275481.1400 275627.0200
2   2010-03-11 00:00:00.000 2010-03-12 05:59:02.937 1   02              202             HS                      1041.9500   2471.9500   3496286.9500    3501872.4400

........... (more than 1000 rows)

but i have onle cur.VrdModTim date as '2010-03-11 05:57:31.000' and i want to filter this data by cur.VrdModTim(2010-03-11 05:57:31.000),so that i can get only 5 records.

Answer 1

Sure it is:

SELECT  1
WHERE   GETDATE() LIKE '%2010%'

However, I believe that for your task there would be a more efficient method.

What exactly is your task?

Update:

To find all records within a given second, disregarding the milliseconds, use this:

SELECT  *
FROM    mytable
WHERE   mydatefield >= '2010-14-09 5:54:45'
        AND mydatefield < DATEADD(s, 1, '2010-14-09 5:54:45')

This will be much more efficient, provided that you have an index on mydatefield.

Answer 2

I think you are better off with the BETWEEN operator.

For example (from MSDN):

SELECT BusinessEntityID
     , RateChangeDate

FROM HumanResources.EmployeePayHistory

WHERE RateChangeDate BETWEEN '20011212' AND '20020105'