January 21, 2005 at 6:22 am
Can anyone please explain to me how I can select from a table against a datetime column where I want to retrieve all rows with date = yesterdays date. I am only concerned with the first part of the date i.e. 2005-01-20
Select * from table where date = ?????(yesterdays date)
the date column is of datetime datatype
i.e. 2005-01-20 11:12:24.765
January 21, 2005 at 7:03 am
Select * from dbo.MyTable where
dateadd(d, (datediff(d, 0, YourDate)) , 0)
=
dateadd(d, (datediff(d, 0, GetDate() - 1)) , 0)
Quick explanation :
datediff(d, 0, YourDate) counts the number of days since 1900/01/01
it then adds (with dateadd) this total of days to 0 (1900/01/01).
All that does is chop the time part of the date.
Then the same process is applied but with GetDate() - 1 to get yesterday's date.
This is a fast operation as there's no conversion between numbers and chars. But it's a good idea to put an explanation besides it in case you forget how it works.
January 21, 2005 at 7:49 am
Remi,
thank you very much, that has worked a treat.
T.
January 21, 2005 at 8:01 am
Please make sure you understand the logic. It's really not as obvious as it seems. It took me at least one hour to really catch this trick and be able to explain what's going on under the hood.
January 21, 2005 at 11:41 am
Hi,
I initiated a similar discussion several days ago, and I got many good answers how to get just a date out of DateTime. I am doing like this now (I have different date limits, try this for yesterday:
AND MyDate >= Convert(smalldatetime, Convert(varchar(10), Getdate (),101)) - 1
AND MyDate < Convert(smalldatetime, Convert(varchar(10), Getdate (),101))
The inner convert statement converts to string without time, and the outer convert converts back to time but this time will be 00:00:00:000, the very beginning of today. So -1 will be the very beginning of yesterday.
The other advice was to use just one convert to string but use not 101 for style but the one that would convert to yyyy/mm/dd so it would sort correctly. You will have to convert on both sides of the comparison operator.
Yelena
Regards,Yelena Varsha
January 21, 2005 at 12:10 pm
Yes yelena, your version will work just fine but since it converts to varchar and back to datetime, it takes more time to execute.
Your version does something like cast((cast as varchar) as int) instead of simply doing simple mathematical calculus. This is why it's gonna take way more time to execute on very large datasets (almost no difference on a few 100s lines).
January 21, 2005 at 3:20 pm
Remi,
You are certainly right. Your way should be much faster.
My query runs towards a table that will not probably be very big. It contains only current records that will be moved into the history records when closed, so the table should not grow big.
The reason I use my way is because I have not only to select but to group by day as well. You may say that your way will allow grouping too. But I work through the Business Objects Report Designer with grouping and I have to do grouping not in SQL but in the front end. That I don't know as well as SQL.
But, again, as was pointed out in previous discussion we can convert to string only once and not convert back to date if we use yyyy/mm/dd string format to sort properly. I may modify my query as well in the future using that or your way.
Thanks.
Yelena
Regards,Yelena Varsha
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply