Voice of the DBA

Category: Blog

  • Adding Minutes in Excel

    A short one, but I found myself wasting time recently. I use an Excel sheet to schedule some items out each day. It’s not my choice to do this, but the service I use requires this to not manually set time for each event.

    Tl;dr: Add minutes/1440 to the date.

    The format looks like this:

    2016-02-05 09_42_25-Settings

    I’ve been changing the dates each day to the next day, which involves picking the cell, hitting F2 (or clicking the mouse), moving to the day and editing it to increment. In this case, I’d go to the 10th for the next set of scheduled items.

    Manual, time consuming, and when I saw this cartoon, I knew I needed to do something else. It’s a few minutes of my day, and really, it’s 12 hours over 5 years.

    is_it_worth_the_time

    But it’s incredibly annoying and tedious. It makes my blood pressure rise, and I delay the work because of it.

    I spent 5 minutes considering a few things. I knew I could do any of these, but some would require more work than others, and perhaps not be worth the time.

    • Use a macro to change each entry
    • Get my source system to generate an Excel sheet in this format
    • Write OLE automation to generate a new sheet

    In the end, the simple solution was just learn to add time to a base date. If I take the first entry, I can see that I’m really adding minutes (or hours) to this one. So I decided to do that. In this way, I still need to edit the first cell, but then all the rest will work.

    However I needed to add minutes to an Excel entry. I wasn’t sure how, but a quick Google search gave me the answer from Superuser. Because of how Excel stores dates, I really need to add minutes divided by 1440 to the date.

    So my first date is: 02/09/16 14:05. To get my next entry, which is 02/09/16 14:22, I need to add 17 minutes, but the forumla looks like this:

    2016-02-05 09_50_19-Settings

    As you can see, that’s the right time. I now had to spend about 10 minutes setting the formula for each time, which is a matter of calculating the minutes for each new time. It’s not too hard, and my ten minutes spent here won’t pay off in time for a long time, especially after this blog post, but it will reduce my stress quite a bit.

    Every day.

  • The Next DLM Demo Webinar–Feb 23

    A quick note that I will be presenting a webinar on Feb 23rd at 10:00am MST. That’s noon EST and 5:00pm GMT. You can sign up here if you’d like to watch. This is similar to other DLM webinars I’ve done that look at the Redgate stack of products that help you develop database software in a smoother fashion.

    I am changing things up a bit. This time the stack is:

    • Git – version control
    • Team City – Continuous Integration or Build server
    • Octopus Deploy – Release Management tool.

    As with the other webinars, I’ll show how I track the version of the database in each environment, then show how to make changes in SSMS, commit them to my VCS with SQL Source Control, and then deploy them in an automated fashion across various environments. In this case, I’ll have this flow of environments

    • development
    • integration
    • test
    • acceptance
    • production

    It’s an impressive demonstration. As I’ve watched this grow at Redgate, I am impressed with how well things work. I wish I’d have had these tools when I built database software earlier in my career.

  • The Easy Phone Restore

    This is hardware week for me, with a new phone coming in the mail. This was a warranty replacement for one whose battery is dying. I can start the morning at 7am with 100% charge and by noon my iPhone is asking to go into Low Power Mode at less than 20%. Fortunately I have a warranty program with my carrier, so they just sent me a new one.

    I was a bit amazed (again) at how easy it is to move and replace an iPhone. To be fair, my wife got a replacement for her Nexus, and it was about as simple. Both of us use cloud backups, and with an easy restore, the vast majority of our phones are back in service, icons, apps, settings the way they were. Switch SIM cards, and the new phone just works.

    Of course there are some passwords to re-enter and new authentication, but it’s very minimal and once I get my password manager running, things go smoothly.

    I wish it were as simple to upgrade a laptop. Instead there are numerous applications to install and get configured. Fortunately I’ve got a process that worked well last year, and I’m getting the chance to refine slightly this year. I’ll be posting a bit about that as I move forward.

    Now I’m hoping that this phone works a little better than the last one and gets me through some of those long, SQL Saturday days.

  • CONVERT and HEX

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

    In working through the Advent of Code and solving some of the problems in SQL, I found that I needed to take hex values and convert them to strings. In other words, I had a value like this:

    select @hex = 0x3c044139f4fe36d7df0f4e87f948fc52

    and I needed to determine if the first few characters (5 or 6), were 0s. In other words, I wanted to look at this part of the data above as a string.

    3c044

    I thought this would be simple. I tried this

    select @value = CAST( @hex as varchar(50))

    That’s my default, as it reads nicely. However the returned this:

    <A9ôþ6×ßN‡ùHüR

    That’s strange. I then tried CONVERT:

    select @value = convert( varchar(50), @hex)

    I got the same result. Why am I not getting the same value as a string? I looked at a few other code samples from others, and they looked the same, so I checked the documentation for CONVERT. I saw this:

    Binary Styles: When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

    Under the table, the information for 1 or 2 as a style has this:

    If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised.

    …

    The characters 0x will be added to the left of the converted result for style 1.

    All of that essentially means that if I use the default, 0, or have nothing, I get the binary data converted to to the binary bytes in ASCII. If I use 1 or 2, I get the string. Here’s a shot of the difference:

    2016-02-02 11_00_14-Settings

    Two lessons. First, learn the data types and how they convert. Second, read the documentation carefully when things don’t work as expected.