Using the DATEDIFF function to select rows

,

I keep seeing people using CONVERT to convert a date to a varchar in order to do comparison or search queries.  I haven't seen a situation yet where DATEDIFF can't be used. 

-- select all rows from before today
-- because 'd' has been specified for days we get back the
-- number of days difference.  negative number means that 
-- sdate column is before today
select * from tablex
where datediff(d, getdate(), sdate) < 0


-- select all rows with date in this calendar month
select * from tablex
where datediff(m, getdate(), sdate) = 0


-- select all rows from last year
select * from tablex
where datediff(yyyy, getdate(), sdate) = -1

--NB.  if you want to select rows that have todays date use 
-- datediff(d, ...) but if you want to select rows from 
--within a day (ie last 24 hours) use

select * from tablex
where datediff(hh, getdate(), sdate) between -23 and 0

Rate

Share

Share

Rate