I live in Windsor, the Canadian border city with Detroit. People cross both ways all the time for entertainment, dining, shopping, sports, etc. I hear the same happens with the border between Niagara Falls and Buffalo. Don't think it's as popular a day-trip pass-time in the prairies though.
It has it's ups and downs, Detroit is pretty huge compared to us, so the tourism money helps, but we also get a lot of 19 and 20 year old's coming to take advantage of our lower drinking age that sometimes cause a ruckus. We've also got a pretty big casino many American's like.
Going the other way, Detroit's got a lot of major sports teams that we Canadians will go see; and they attract a lot bigger acts then we would get. Our local public transit company even runs a "tunnel bus" that has a route going across the border that will make special stops for those events. The Detroit airport is also the one we would use to go just about anywhere outside of Canada.
Some people will cross the border to shop based on which currency has the better exchange rate. We've also got a lot of people that will live in Canada, but work in the US, commuting across the border every day. Don't know anyone who does the opposite though.