SQLserver date manipulation and formatting

MicroSoft SQLserver has some pretty slick features, but one area where they are definitely sucking is date manipulation and formatting. [Joseph complained about this](http://joseph.randomnetworks.com/archives/2006/06/22/sql-server-convert/) a while ago, but it’s time to me to chime in now.

My biggest complaint is the half-assed implementation of a reasonable design: Instead of having separate “date” and “time” datatypes, they provide a single “datetime” type. As far as I’m concerned that’s a good thing since it merges three separate types into one. But it’s an incomplete implementation because there isn’t a simple way to extract the date or time from a datatime type. There *are* functions to extract individual “fields” of the date or time, e.g. day of the month, hour of the day, etc. What I’d prefer would be a function(s) that would return *only* the date and/or time from a datetime agglomeration.

I’m currently trying to write a query that goes back to the beginning of the sixth month in the past, in order to implement a “recent orders” view. If I use the dateadd() and getdate() functions, I can get pretty close to my goal:

select dateadd( mm, -6, — Go back 6 months from…
getdate() + 1 – day(getdate())) — 1st of this month
))

The problem with the code above is that it returns the correct *date*, but the time part will be set to whatever time this code was executed. If I run this query at 12:34 in the afternoon, I won’t see orders placed on the morning of the first day I wish to report. The easiest way I’ve found to get around this to use convert() *twice*: once to strip off the time (returning a varchar), and again to convert the varchar back into a datetime:

select convert(datetime, — Convert back to datetime
convert(varchar(11), — Truncate off the time
dateadd( mm, -6, — Go back 6 months from…
getdate() + 1 – day(getdate())) — 1st of this month
))

The default format for converting datetime to/from varchar is “Mon DD YYYY HH:MIAM”, so above I’m grabbing the first 11 characters (“Mon DD YYYY”) of the converted string and passing them right back to convert() and asking for a datetime. Since I haven’t supplied a time, it defaults to midnight.

Posted in Uncategorized | Tagged , , | Leave a comment

Salvaging hardwood floors, other progress on the house

We were pretty busy last weekend working on the remodel, as usual. Most of the stuff we did was prep work for our tile setter, but even though we put in about 50 hours this weekend we didn’t finish *any* of the tasks on our [punch list][]. However, we did get to 75-95% completion on eight of our punch items. The only thing we *did* get to cross off the punch list was our HVAC grills, etc. José came out on Friday and installed the grills and our new thermostat. Oh, and one more thing that wasn’t on the punch list–We were finally able to get rid of the “Blue Office”, as one of the crewmen referred to it:

[punch list]: http://sonicchicken.net/blog/wordpress/?page_id=406

100_0657.JPG

So, what did *we* do?

– Salvaged hardwood from the entry — We have about 150 ft2 of 60-year-old white oak which we’re going to replace with tile. We also added about 115 ft2 of new floor space to create the great room, most of which is already covered with the exact same flooring. Since we’re trying to use [green building][] techniques as much as possible, we were obviously interested in reusing the old hardwood for the new floor. Our hardwood contractor, [BeautiFloors](http://www.ibegin.com/usa/california/auburn/beautifloors-.html), also told us that salvaging would give us the best match with our existing floor. The salvage process deserves an article of its own, but I don’t have time to write it now. We managed to get nearly all of our hardwood up — we only have 2-3 boards on each side of the entry which we still need to pull, and we’ll probably get them done tonight.
100_0672.JPG

[green building]: http://en.wikipedia.org/wiki/Green_building

– Screwed down the old subfloor
100_0685.JPG
When the house was built back in the post-war years, they used 1×6 boards laid diagonally for the subfloor. The boards were nailed (not screwed) to the joists, without subfloor adhesive. After ~60 years, the old subfloor is loose and squeaky. It wasn’t feasible for us to replace the boards with modern T&G plywood, but 10 pounds of [deck screws](http://www.flickr.com/photos/sonicchicken/2083953205/) and a few hours of labor was a reasonable investment. If there was any question in our minds about the efficacy of this, it was quickly answered once we got the second row of screws in–the difference between the screwed-down side and the nails-only side of the joist was extremely evident! As we were working, we started to notice that the nailed floor seemed soft or bouncy, whereas the screwed-down area felt stable and solid (and no longer squeaky).

– Repaired the original floor — Our house originally had an under-floor heater, which was replaced several decades ago; however, the holes for the heater and flue were still there. We’re laying tile over these holes, so we had to patch them.

100_0689.JPG
100_0692.JPG

– Laid out [Hardiebacker][] for the floor tile — We’re saving some money on the tile installation by doing some of the no-brainer work ourselves. Laying the Hardiebacker is pretty simple, but time consuming, considering we’re putting about 700 ft2 of tile in the entry, nook, kitchen, utility/laundry room, bathroom, and part of the hallway. We’ve got 41 of the 3′ x 5′ sheets already cut and laid out on the floor, leaving us with 4 or 5 more sheets to finish the job. Once it’s all cut and placed, we’ll start mixing up batches of Thinset and applying it under each sheet.
100_0697.JPG
The major downside of this is that we had to take down our kitchen and laundry facilities *again*. Our tile setter assures us that we should be able to set up our laundry and kitchen (for keeps, this time!) on Sunday.

[Hardiebacker]: http://www.jameshardie.com/homeowner/backerboard.shtml

– Painted the old front door — Eventually we’re going to install a new front door, but until that happens we’re stuck with [the old door and its unattractive brown/green color](http://www.flickr.com/photos/sonicchicken/2083954631/in/set-72157603089665346/). There are quite a few homes in our neighborhood with brightly colored front doors, and Barb and I have always fancied the look. A bright red would be very attractive, but it’s the most common color in the neighborhood. Since the house is a nice light yellow we thought a contrasting color would look good, so we’re going to try a dark, rich purple. So far we’ve only got one color coat applied, and it’s going to take at least one more coat before I’ll try taking a “final” picture.

Posted in Our home remodeling project, Uncategorized | Tagged , , , , | 6 Comments

Difficulty upgrading to Leopard 10.5.1

I upgraded my PowerBook G4 to [OS X “Leopard”](http://www.apple.com/macosx/) yesterday. (Thanks, Kasandra!) Looks pretty cool, but one of the first things it tried to do was auto-update to 10.5.1. The system would present a “You must restart to apply this update” message, but upon restarting the system would hang while displaying “Configuring Installation”. After three attempts (and concomitant reboots) I finally gave up and asked Google for help. It turns out that there is a (much larger!) [“standalone” 10.5.1 upgrade](http://wsidecar.apple.com/cgi-bin/nph-reg3rdpty2.pl/product=16133&cat=60&platform=osx&method=sa/MacOSXUpd10.5.1.dmg) available from Apple’s [Mac OS X 10.5.1 Update](http://www.apple.com/support/downloads/macosx1051update.html) page. This did the trick, so now I can actually start exploring Leopard!

The best new feature I’ve seen so far is the [Cover Flow view in Finder](http://www.apple.com/macosx/features/finder.html), a very slick way to browse your files!

Posted in Mac OS X, Uncategorized | Tagged , , | Leave a comment

Punch list

Our remodel/addition project has gotten to the point where it’s time to make a [punch list](http://sonicchicken.net/blog/wordpress/phase-1a-punch/). We (and our subs) still have quite a bit to do, but [this phase (1A)](http://sonicchicken.net/blog/wordpress/2007/06/21/remodel-master-plan/) is definitely nearing completion!

Posted in Our home remodeling project, Uncategorized | 2 Comments

Pictures!

I uploaded quite a few pictures to our [Flickr account](http://www.flickr.com/photos/sonicchicken/) today and created some new photo sets.

Barb and the girls in the new kitchen

The entry gable and front porch at browncoat stage

I also added some more pictures to the [painting photoset](http://www.flickr.com/photos/sonicchicken/sets/72157603089665346/).

Posted in Our home remodeling project, Uncategorized | Tagged , , | Leave a comment

Week 32.5

It’s been about 32½ weeks since construction began on our remodel, and we’re getting down to the finishing touches. We (and our subs) have been pretty busy, which explains the paucity of articles recently. Barb and I have taken a few pictures, but I haven’t had a chance to pull them off of the camera yet.

The drywall is finished except for a rough patch job in what will eventually be a closet. The texturing is done except for sanding in the old dining room.

Painting is complete in the entry, great room, kitchen, laundry/utility room and bathroom. Caitlin’s bedroom is painted except for the closet. Caira has two walls finished, one wall needs to have the edges cut in, while the fourth wall and closet still need their color coats. We can’t start painting the old dining room until the texturing has been sanded, and Barb ran out of paint while working on the bedroom hallway. Unfortunately this was *after* the paint store had closed for the weekend, so we’re stuck until the paint store re-opens and we have time to resume painting.

Our kitchen and utility room cabinets arrived last week. Our cabinet maker works up in Chico, so he delivered the cabinets and stayed in town overnight to do the installation. We got Shaker style cabinets in natural cherry, and they look fantastic, but unfortunately we can’t load up the lower cabinets because we’ll just have to empty them next week when the countertops are installed. 🙁

The third and final coat of stucco was applied on Wednesday, right before Thanksgiving. It’s amazing how much of a difference it makes! I don’t know whether it’s due to the finished texture or the colored stucco (or both), but the house exterior really looks finished now. (It’s not, of course. I still have to install exterior light fixtures, clean up all the construction debris, and begin the arduous task of post-construction landscape remediation. Groan…)

Once Barb has finished painting a room I’ve been coming along behind her to take care of the electrical finish work, (e.g. installing outlets, light switches and fixtures, etc.) But it’s a sad fact that she can paint faster than I can do all the wiring, especially when I drop what I’m doing to help her paint! :/ I’ve been putting most of my effort into getting the lights working, and I’m almost finished except for ceiling fans/lights–I’m waiting until our HVAC contractor installs the registers and air return grills because the ceiling fans will be in his way otherwise.

This coming week is going to be another busy one. Our tile setter will be back from vacation, and he’s supposed to start working on our project next Saturday, so the goal for this week is to have everything ready for him then. I have to return to my day job after being off this past week, so that’ll cut a huge chunk out of my remodeling time. Our plumber will be out mid-week to remove the drain and water supply from the old kitchen sink. Barb and I have to salvage about 150 square feet of the hardwood flooring from the entryway, then we have to put ~45 sheets of [hardiebacker][] in the entry, kitchen, nook, hallway, laundry/utility room, and bathroom. Oh, and I still need to do electrical finish work. No rest for the weary!

[hardiebacker]: http://www.jameshardie.com/homeowner/backerboard.shtml

Posted in Uncategorized | Leave a comment

Paint colors & techniques

Barb thought it might be a good idea to document the paint colors and techniques we’re using, and what better place to do that than the blog? We’re doing a 3-coat process–sealer, primer, and color.

The sealer we’re using on the new drywall is a product called [SuperHide][]. It keeps the primer from getting sucked into the bare wallboard. We normally wouldn’t need a sealer on the old plaster since it has already been painted, but the people who lived in the house for ~55 years before us smoked. A **lot**! Click on the photo below for a higher resolution, if you dare…

100_0554.JPG

We’re using [Zinsser 1-2-3][zinsser] to seal the nicotine into the old plaster and keep it from staining our new paint. (Kind of gross to think of it that way. Makes me want to tear out all the old plaster, but that’s impractical.)

Once the sealer has dried, we’re applying the primer. For the walls, we had our paint supplier add some tint to the primer, so that it’s easy to distinguish the primer from the sealer. (Otherwise we’d be painting white-on-white.) Since our ceiling color is almost white, we can’t tint the ceiling primer.

The final step is the color coat. We’re using [Aura paint][] throughout the house. It’s pretty expensive but well worth it! We first tried the Aura line when we painted our living room last year. We like it because:

1. One-coat coverage — No second color coat (and cleanup!) required.
2. Bullet-proof touchup — If we miss a spot, we just paint it later. Once the patch is dry, it blends in perfectly with the rest of the wall/ceiling.
3. Coordinated palette — I don’t know how to explain this, but all the Aura colors “go” with each other.

We’re getting all our paint from [Pryde’s Paint and Paper][pryde]. (Annoying web site, but the staff is excellent!) This is one of the few places where we had to present any documentation in order to qualify for a contractor’s discount, but the discount is *absolutely* worth the effort. Pryde’s regular prices are a bit steep, especially for the high-grade paint and primers we’re using, while the discounted prices are competitive with Home Depot. (And of course, Home Depot’s best paint can’t hold a candle to the stuff we’re buying from Pryde.)

Here are the colors we’re using. Sadly, I can’t just link directly to the color pallet on the Aura site, so the best I can do is give the color name below:

* Ceilings (whole house) — Sonnet
* Trim (whole house) — Mascarpone
* Living room — Glacial Till
* Entry & hallway — Truffle
* Game room — Soleil
* Great room — Croquet
* Kitchen & nook — Solitude
* Laundry/utility room — Buttered Yam
* Bathroom — Fiji

[pryde]: http://www.prydesofsacramento.com/home/index.html
[SuperHide]: http://tinyurl.com/2hy76o
[zinsser]: http://www.zinsser.com/ “‘Connection reset’ errors on 2007-11-11”
[Aura paint]: http://www.myaurapaint.com/#about “Warning! Flash-based website, not very pleasant to use, and I can’t link directly to the colors”

Posted in Our home remodeling project, Uncategorized | Tagged , | 2 Comments

Painting

After nearly seven months of construction, we’re finally starting to paint! The garage, which required the least drywall compound, is ready for painting. Because this is all fresh drywall, we’re putting on two coats of primer–the first one basically seals the drywall, while the second coat actually primes the wall for the color coat. Even though it’s not quite a two-car garage, it’s going to take a fair bit of paint because we have 9′ high walls *plus* the ceiling. Barb picked up the primer(s) and the color paint this afternoon, and she has already started painting. I’ll be joining her as soon as I finish posting this, and Punk is coming over after work to lend her expertise as well.

We’ll get some more painting done over the weekend. According to our drywaller, the texture on the laundry/utility room and the new bathroom should be cured and ready for paint by Sunday morning, possibly even Saturday afternoon. He’d like us to paint those rooms so he can compare his texture to the 60 year-old texture on the plaster. I think we’re going to put more effort into the utility room, since we’d *really* like to get the washer and dryer back into operation! (Laundromats are definitely sucking!)

Posted in Our home remodeling project, Uncategorized | Tagged , , , | Leave a comment

Another inspection, more drywall work

We have another inspection(s) today–gas pressure test and stucco scratch. The stucco inspection is no big deal, the inspector just walks around looks at the exterior of the house. The gas pressure test is kind of a drag, though. We had to disconnect *all* of the gas appliances, so we had no heat, no hot water, and no stove today. Fortunately our GC sent a couple of guys over this morning after we had all gotten ready for work/school, and they capped off all the appliance lines. Once the inspector shows up, Barb will call the GC and have them reconnect everything relatively quickly.

Our drywall crew has been working all week on taping and topping the wall board. The seams were all taped the first day (Monday), and they’ve been feathering out the seams and all the patches that were done in the old plaster. It’s been kind of frustrating for us, because there’s not much we can do right now. We’re just waiting for drywall compound to cure; meanwhile, we’re itching to start painting.

Update 2007-11-10 00:23 — I just heard from Barb: we passed all of our inspections. (I had forgotten about the drywall shear inspection in the list above.) As far as I know, the next one (on the addition/remodel) is **final inspection**!

Posted in Our home remodeling project, Uncategorized | Tagged , | Leave a comment

Stucco scratch coat

It’s sure handy being one of the few people building or remodeling a home at this point. I called Jack, our stucco subcontractor, yesterday afternoon to see if they could put the first coat on the house now, seeing as we didn’t have the shop ready yet. Jack: “No problem. Is the drywall done?” Well, they’re still taping and texturing, but the drywall is up and they’re done hammering if that’s what you’re asking. “Okay, I’ll have a crew out tomorrow morning!” Uh, Jack? We have a whole bunch of stuff right next to the house. Maybe you can give us a few days to move everything? Jack: “Don’t worry about that, the crew will move it for you.” Wow! About 8-10 people showed up this morning at 07:00, and the scratch coat was done before I came home for lunch at 12:45, less than 24 hours after I called.

100_0531.JPG

We’ll have to wet down the stucco a few times per day while it’s curing, especially on the south side of the house:

100_0524.JPG

Barb has Friday off, so we’ll probably call for our stucco scratch inspection that day.

Posted in Our home remodeling project, Uncategorized | Tagged | Leave a comment