Voice of the DBA

Category: Blog

  • Achievement 2020: Holiday Use

    One of the company mandates this year was to use 60% of your holiday (vacation) allowance by Sep 30. This was to prevent lots of people wanting to use time off in Q4 and many of them having to be told no because we have a business to run. When this came out, it looked like the world might open up more in Q4. Not sure that will happen, but preparation is important.

    I’m taking today and tomorrow off, and that will put me at 66% of holiday for the year.

    I struggle with taking time off, so I’m proud that I made it, even with a pandemic and no travel occurring.

    Today is a horseback ride with my wife and daughter. Tomorrow are chores, but not too intense ones.

  • Daily Coping 24 Sep 2020

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

    Today’s tip is to don’t compare how you feel inside to how others appear outside.

    I used to compare myself to others early in my career. The jobs they held, their salary, the projects they’d built or managed. Really, anything that others had accomplished. I think that can be useful early in your career to give you things to strive to accomplish.

    Where this falls down is when you overvalue more than a specific goal from someone. While I might have wanted to manage a team of DBAs, or build software that provide real time analytics, I shouldn’t think that someone else’s path is the one I want to walk.

    This can be the same phenomenon on Instagram, Facebook, etc. where it appears others have amazing lives and you don’t. Others might be loving their experiences when you’re struggling with the world.

    I post the fun things and interesting experiences, but I don’t talk too often about the struggles between these amazing moments.

    You don’t see the tough times or thoughts.

    At least not many. I do expose more than a few friends think I should, but overall I keep a lot private, between family and friends. I have down times, I have difficult times, I question what I’m doing, how and why, on a regular basis.

    My internal world is different from my external world. I try hard to remember that when I see things from others that might make me jealous of them.

  • Daily Coping 23 Sep 2020

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

    Today’s tip is to notice what you are feeling today without judgement.

    I write this ahead of time, so I’ll tell you about my day today. It’s mid morning. I have been awake for awhile, working along, handling a variety of communications with different groups and people. It’s a hectic am, and my body is sore. A lot of workouts and ranch chores lately, and I’m fatigued.

    However, I’m actually doing well. I followed this advice without knowing it this morning. I woke up, knowing I was tired, but needing to get to work. I accepted the soreness and feelings, choosing to work with them.

    A few naproxen, which is a rarity for me, but I do use them when I’m especially uncomfortable. I had some time, so I treated myself to a coffee run in town. Not very efficient or cost effective, but it sparked joy, so I did it.

    Then I went to work, accepting and handling things as they arose, even with a number that I hadn’t planned on needing to complete today.

    Go with the flow is something I do often, even when it’s stressful on busy days, with lots of things that interrupt other items I need to handle.

  • Cleaning up bad dates–#SQLNewBlogger

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

    I got some data recently from an online service, MapMyRun.com, where I track my workout data. I’ve been doing this for years, but with some of the instability and security issues with services, I decided I need to periodically grab a copy of my data and load it.

    This post describes an issue with cleaning up the date data.

    When I tried the load from SSMS (Tasks->Load Flat File), I got errors if I attempted to insert into a date field. My goal is to use the pattern of loading to a staging table and then merging data into my main table, so I decided to just load into a staging table. When I did this, here is what I saw.

    2020-08-27 12_30_53-~vs7264.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (58))_ - Microsoft S

    For a human, the dates make perfect sense. For a computer, however, translating this to a date via an implicit conversion doesn’t work well. No problem, I can fix this. I’ll replace the date:

    SELECT top 10
     CAST( REPLACE(swh.Date_Submitted, 'Aug.', 'Aug') AS DATE) AS SubmitDate
     , *
    FROM dbo.staging_workout_history AS swh

    This works great.

    2020-08-27 12_33_28-~vs7264.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (58))_ - Microsoft S

    However, if I scroll through the entire file, I find issues with other dates. Once I remote the top, I get this:

    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting date and/or time from character string.

    Hmmm, there are other issues. I could remove my CAST and scroll through, but there’s an easier way. I’ll use TRY_CAST() instead. If I restructure my query, I can run it to completion.

    2020-08-27 12_40_27-~vs7264.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (58))_ - Microsoft S

    Now I can scroll through. I could also add a test for getting NULL from TRY_CAST to find the problematic dates. Here’s where I see more problems.

    2020-08-27 12_41_08-~vs7264.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (58))_ - Microsoft S

    It seems that whoever exported data, from whatever system, decided periods in months make sense. I can amend the REPLACE in this way, which should fix things.

    SELECT
     TRY_CAST( REPLACE(swh.Date_Submitted, '.', '') AS DATE) AS IsItADate
     , *
     FROM dbo.staging_workout_history AS swh
     

    Now I see this seems to work

    2020-08-27 12_46_22-~vs7264.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (58))_ - Microsoft S

    One More Problem

    I’m glad I kept scrolling through a full year. That’s because I saw this:

    2020-08-27 12_47_33-~vs7264.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (58))_ - Microsoft S

    Who thinks Sept is the abbreviation for September? Technically it is, and I see April, June, and July spelled out, so the inconsistency in this extract is bizarre to me. Perhaps there is a database that formats English month names like this and deals with conversions, but this seems like sloppy programming to include “April”, “Jan.”, and then “Sept.” in your data set.

    In any case, I can add another REPLACE() to my code.

    SELECT
     TRY_CAST( REPLACE( 
                       REPLACE(swh.Date_Submitted, '.', '')
                       , 'Sept', 'Sep') AS DATE) AS IsItADate
     , *
     FROM dbo.staging_workout_history AS swh
     WHERE  TRY_CAST( REPLACE( 
                       REPLACE(swh.Date_Submitted, '.', '')
                       , 'Sept', 'Sep') AS DATE)  IS null

    This converts all the dates in my column to the expected value. Now I need to do the same thing for other date columns, change TRY_CAST() to CAST() and I can import data.