I have a column called createdate of datetime datatype. I want to extract data between today and yesterday.

select * from table1(nolock) where createdate ='2018-06-01' --not getting any output

I want something like this:

select * from table1(nolock) where createdate between '2018-06-01' and '2018-06-02'

Rather than hard coding the date, I need between dateadd(d,-1,getdate()) and getdate(), but getdate() gives current timestamp. How can I search for the whole day like 2018-06-02 rather than 2018-06-02 02:12:13.423?

  • As a side note, please don't use nolock hint unless there's a good reason for it - and you are well aware of its side effects. – vonPryz Sep 25 at 19:19

You can always find today (with no time) using SELECT CONVERT(date, GETDATE());.

So to find all of the data for yesterday, you say:

DECLARE @today date = GETDATE();

WHERE createDate >= DATEADD(DAY, -1, @today)
  AND createDate <  @today;

For today, it's a simple change:

WHERE createDate >= @today
  AND createDate <  DATEADD(DAY, 1, @today);

Community wiki answer - please edit to improve:

Your condition dateadd(d,-1,getdate()) and getdate() is almost right.

You need for the yesterday value to get rid of time like:


So your condition will look like this:

where createDate >= dateadd(day, datediff(day, 0, getdate()) - 1, 0)  
  and createDate  < getdate()

If you only want things from yesterday, it should be:

WHERE     (txtDateTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (txtDateTime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.