Voice of the DBA

Tag: syndicated

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.

September 23, 2020
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.

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.

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.

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.

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

One More Problem

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

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.
September 23, 2020
Daily Coping 22 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 find a caring calming phrase to say to yourself when feeling low.

This is really needed these days, as I find emotions and reactions amplified by the stress of the world. I have lots of empathy for others, and this affects me. I also find myself regularly annoyed by those that don’t display this, whether online or in person.

What’s more frustrating is that often I know these are people that would display empathy for those they know, but they lose that in the wider perspective the challenges in the world.

“There is a lot of good in the world.”

That’s my phrase. I remember that there are lots of good people looking to help and support others. There are lots of people working to improve themselves and others. There are lots of positive things, even when I disagree with some of the methods.

Despite all those I think aren’t doing this, there are significant numbers that are, and that gives me hope.

September 22, 2020
Better Productivity: Cycling the Windows Clipboard in Windows 10

A quick tip I learned, that has become handy for me.

Tl;dr: Try Win+V

Often I am moving lots of information between different applications. I might do some editing, some code movement, even grabbing notes for reporting feedback to dev teams or something else. I may need 2-3 pieces of info, and make heavy use of the Windows Clipboard.

However, sometimes an app is running slow, and I might end up cutting a second piece of data before pasting in the first one. I ran cross this tip recently, which was really handy.

Using the Windows key and V, I can get a list of a few items that I’ve recently put on the clipboard. While writing this piece, he’s an image (actually a picture) of what my Win+V looks like.

As you can see, I have a few items I can pick from. I can arrow up and down and then hit Enter to insert the pasted item. I can also use the mouse.

You can ensure this is enabled in your control panel (settings). Click Win+I to get this and search for Clipboard. In there, the history item is what you want to enable.

A little trick, but it keeps me from slowing down when I’m trying to get a few things done at once.

September 21, 2020