Voice of the DBA

Category: Blog

  • Closing Down for the Year

    Today is my last day of work for 2015. I’m actually proud that I’ve managed to take almost all my vacation (3 days left), and will be enjoying myself next week with my family, taking our holiday vacation in the mountains.

    It’s been quite a year for me. Lots of travel in short spans, some long trips, but also some good breaks. I’ve spent some time learning, and improvind my skills, and at the same time I turned down lots of work that might have overloaded me.

    I think 2015 was a good year for me, and I’m looking forward to continuing some momentum and making 2016 even better.

  • Getting all Yesterday’s Sales, or Finding Midnight Yesterday

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

    I see questions like this regularly. How do I get all the sales from yesterday? I tried using DATEADD(day, –1, getdate()), but I only get some of the sales.

    Many people working with T-SQL know this is an issue. They know that getdate() returns the date and time of this instant (roughly). At the time of this writing, that’s 3:11 pm.

    2015-11-25 15_11_29-Photos

    However if I want sales from yesterday, I really want all timestamps from midnight on. So I probably want code that looks like this:

    SELECT SUM(ordertotal)
 FROM sales
 WHERE SalesDate > '20151124 00:00:00'
 AND SalesDate < '20151125 00:00:00'

    How do I get the time to be midnight?

    The easy answer is one I’ve been using quite a bit lately to answer questions, and I’ve refreshed my knowledge of the datetime trick. I use a combination of DATEADD and DATEDIFF to get to a 0 based datetime.

    SELECT DateAdd(Day, Datediff(Day,0, GetDate()), 0)

    In this case, I’ll get midnight yesterday, or 2015-11-24 00:00:00. This is because I’m using 0 as my base date and looking for the days (in DATEDIFF) since that 0 based date. When I add those days with DATEADD to the same zero based date, I get the correct date, but with a 0 based time.

    This same technique works to find the first of this month.

    SELECT DateAdd(Month, Datediff(Month,0, GetDate()), 0)

    You can also use other datetime values to normalize those times.

    SQLNewBlogger

    This was a quick post. I had answered the question and spent less than ten minutes putting this together.

  • The Database is the Cornerstone

    I saw a case study video the other day from Redgate Software. It was a look at one of the upper management on Fitness First, a fitness/gym company that has facilities in dozens of countries. It’s a good, short summary of the benefits they’ve seen from implementing DLM and ALM, with the DevOps Guys.

    It’s worth a watch, and I especially like that this manager understands and gets that the database is the cornerstone of better software deployment. That’s his quote. Without being able to be flexible and agile with the database, it hampers your flexibility.

    Fitness First at SQL in the City

    I also appreciate that they do regression testing in an automated fashion. It helps them reduce the number of bugs and speeds their ability to deploy. They also have moved to curated sets of test data to allow them to test known cases and bugs they’ve seen.

    They also “run at speed”. They meet the business needs, they can work quicker to meet client demands. That’s important.

    Fitness First also did a presentation at SQL in the City. I sat in and was impressed, especially with some of the work from the DevOps Guys. It’s long, 40min, but interesting.

    Fitness First Case Study

    I wasn’t sure what to think when it started, but by the end, I think that this is a good view of how DevOps can help. It probably is more valuable to you if you have a backlog of work, but even if you want to develop and deploy at a pedestrian pace, having a DLM/ALM setup can build your confidence and reduce bugs.

    There are a few other short videos that showcase what some of our clients think of DLM and automated database deployment:

  • A Few Days Off in Space Country

    The new Star Wars film opens today, and while I won’t see it then, perhaps I’ll get to it early next week in the shadow of the US space program.

    My middle son is in his last year of high school before he goes off to college and as his birthday present in 2015, I’m taking him down to Florida and Cape Canaveral for 4 days. We leave early tommorrow, not returning to Denver until late Tuesday.

    A couple days of vacation for me, and hopefully some memories for my son. He was there a long time ago, at 2, so he doesn’t remember much. However he’s interested in flight and space, and looking to study aeronautical engineering in college, so this is a treat for him. In fact, when we were talking about where to take him, his eyes lit up when I suggested this trip.

    It will be an interesting trip, with the chance to visit the Kennedy Space Center, meet an astronaut, and study some of the history of US space travel in person.

    I’m also excited to go. Not that I want to go into space, at least not until they get artificial gravity, but I find the subject fascinating and I felt rushed the last time I visited. It will be nice to be able to spend time and not hurry around as we get the chance to vacation together.

    Enjoy your weekend, and I’ll see you briefly next week before I close down for the year.