Opinion: Don't block PO Boxes unnecessarily

In some countries, post office boxes are quite anonymous. And for that reason, some vendors aren't keen to send goods to PO Boxes. But that's not all countries. In Australia, for example, you have to provide all sorts of ID to the post office to be able to get one.

Why PO Boxes?

The fundamental reason that many people use PO Boxes is to have a relatively safe location for their mail to be collected. At so many houses, letter boxes are quite unsafe. And for people living in apartments, the situation is often far, far worse.

Like everyone else, we've been doing a lot more online shopping lately. What has really frustrated me though, are vendors who don't handle address details properly.

Losing sales

I've had many sites who have a rule built into the UI to prevent entering a PO Box for a shipping address. Even though I'd prefer it wasn't that way, I'm OK with that. But then they use the same address validation logic for a billing address.

Please, please, please don't block PO Boxes in billing addresses. That makes no sense.

I've had sites where I want to buy products, and I can't because they won't let me enter my actual billing address (i.e. a PO Box) for the credit card.

At that point, I can't proceed with the purchase.

And identity theft issues

Stealing mail from street letter boxes, etc. is a common cause of identity theft. Yet, I often find that exactly the sorts of suppliers who should be concerned about identity (utility companies, banks, local councils, etc.) often insist on sending mail directly to street addresses. That's not sensible.

Awesome image by Mathyas Kurmann

Worse, I've seen people move to a new address, and the bank sends details of the change, to their old address ! i.e. the place where they are no longer living.  I understand the decision process that led them to do that (in case the move wasn't real) but think what they've just done: 99% of the time, they've sent private bank-related details to an address where someone no longer lives.

Another common situation is where people travel a lot. While that's not an issue for us right now, it is at times. And having mail hanging out of a street letterbox isn't helpful security-wise. We fortunately have good neighbours who will deal with that but the issue is that we shouldn't need them to do that.


T-SQL 101: #70 Adding and subtracting intervals in SQL Server T-SQL using DATEADD

When you're writing T-SQL, you might also need to add intervals to dates, perhaps add days or subtract days and so on. The DATEADD() function is the one that we can add or subtract intervals to the date and time.

Now in the example shown here, what I've said is in 20190228 or  28th of February 2019. I want to add on 12 days. To subtract 12 days, I would have just put -12 instead of 12. You can see the output here:

Now there are a wide variety of intervals. It's not just days. There are values right down to nanosecond.

Notice also though, that there are abbreviations for each of these. So instead of writing day, you could just write d. Or you could write dd, but I'd suggest you don't do that.

You might imagine that minute is m. But m is actually month and so on, so it can get very confusing. For someone who's reading your code, it's much, much better if you actually use the full word. So even words like nanosecond, second or millisecond, just put the whole word.

You'll see lots of old programming where people have used the abbreviation instead. Don't follow that.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Is or Very

Before I started learning other languages, I'd always heard people saying that translating was hard, but I didn't really understand what they meant. When I was about 13, I was introduced to French, Latin, and Japanese. Now that's an odd mixture. I started to see the problems. Directly translating a sentence word by word leads to either nonsense in the other language or clumsy language.

For Chinese, I wanted to cover a simple example today.

In English, we might say:

He is smart.

The direct translation in Chinese would be:

他是聪明。(Tā shì cōngmíng.)

And yes, Google Translate does return "He is smart" for that Chinese sentence. But the problem is that the sentence is wrong. If you just reverse the direction of the translation, Google returns:

他很聪明。(Tā hěn cōngmíng.)

Literally, that's "He very smart". And that's how it's said in Chinese.

And if you've talked to, or listened to, Chinese migrants in the past, you may well have heard them use exactly that type of malformed English.

In Chinese, you don't just structure a sentence like

SubjectNoun is Adjective

That's fine in English but not in Chinese. Similarly:

SubjectNoun Adverb Adjective

isn't OK in English either.

(Hěn) meaning "very" is a common adverb but others could be used, to vary the intensity.

Incidentally, an adverb is called a 副词 (Fùcí) which is literally a secondary or auxiliary word.

Learning MandarinI'll write more soon on the best methods for learning Mandarin. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

SQL: What is the difference between connecting to .\SQLEXPRESS and (localdb)\v11.0 ?

I keep hearing questions from developers about the difference between SQL Server Express Edition and LocalDB. One asked me the other day:

What's the difference between connecting to .\SQLEXPRESS and (localdb)\v11.0 ?

SQL Express (it's really SQL Server Express Edition) is a service-based version of SQL Server i.e. it runs as a service all the time, independently of other applications. When you say .\SQLEXPRESS you are looking for a named instance of SQL Server called "SQLEXPRESS" that is on your local machine and connected to via a shared memory interface (that's what the dot is).

Local DB is a deployment option for SQL Express that runs as an attached process to another application, instead of as a service. It makes it easier to deploy applications like Visual Studio that need a copy of SQL Server locally but don't want to be heavy-handed by installing a service as well as the IDE. You can read about LocalDB here.

Visual Studio also just needs to use a SQL Server for a while during development, but doesn't need it running all the time. When you connect to "(localdb)\v11.0" you are connecting to one of these attached instances, and in this case using version 11. When you start it by accessing it from within Visual Studio, it shuts down again when you close Visual Studio.

It doesn't have to be attached to Visual Studio though. You could attach it to your own application. You can also have multiple versions of localdb on the same machine.

You can even start and stop Local DB separately from applications like Visual Studio. The SqlLocalDB Utility can be used to manage the lifetime (starting and stopping) of Local DB. You can read about it and the functions that it offers, here.

SQL: Adding time to Change Data Capture (CDC) Events

Several times now on forums, I've seen questions about Change Data Capture (CDC). People like the way they can retrieve details about changes that have occurred (often to trickle-feed into a data warehouse), but they are puzzled why CDC doesn't tell them when the event occurred. There's an easy fix for that.

Let's start by doing a quick CDC setup:

And let's then add three rows of data, then change one of them:

There are two ways we can see the changes:

That returns the following:

The first SELECT returns all changes as they occur, and the second SELECT returns the net affect of those changes. Because NewEmployeeID #1 was inserted as Fred Jones, and later changed to Greg Low, the net affect is just of an insert as Greg Low.

But notice, there's no time value. What we can use to get the time is the LSN (log sequence number) by calling the sys.fn_cdc_map_lsn_to_time() function like this:

Now that returns:

You can see that the function returns us the time. The time is in local time for the server.

If you want to tear the example back down, just do this:


SDU Tools: Date Dimension Period Columns in SQL Server T-SQL

The DateDimensionColumns function in our free SDU Tools for developers and DBAs, has been really popular. It provides the standard columns that are part of a typical date dimension in a data warehouse. But we're especially proud of a new function that can be used to expand a date dimension with really useful additional information. We call it DateDimensionPeriodColumns.

The DateDimensionColumns function had the usual columns:

Date, Day Number, Day Name, Short Day Name, Month Name, Short Month Name, Month Number, Month Label, Year, Year Label, Day of Year, Fiscal Month Number, Fiscal Month Label, Fiscal Year, Fiscal Year Label, Day of Fiscal Year, ISO Week Number

But the DateDimensionPeriodColumns function adds many more useful details:

Is Today, Is Yesterday, Is Tomorrow, Is Future, Is Working Day, Is Last Working Day, Is Next Working Day, Is Weekend, Is Same Month, Is Month to Date, Is Same Month Last Year, Is Month to Date Last Year, Is Same Calendar Year, Is Calendar Year to Date, Is Last Calendar Year, Is Last Calendar Year to Date, Is Same Fiscal Year, Is Same Fiscal Year to Date, Is Last Fiscal Year, Is Last Fiscal Year to Date

That should let you say bye-bye to a whole lot of tricky DAX; it's easy to just use those columns for relative or period filtering

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:


Latest version of the code

Note: the code might wrap when displayed below.


Opinion: Does a human respond to your website contact requests?

Most websites that I visit have a link at the bottom of the page that suggests that you can use it to contact either the website team or the company that owns the site. (Might not be the same people) Based on years of trying, my expectation of ever getting a response from using one of these links is close to zero.

If you have a website that has a contact link, does it lead anywhere sensible?

Does it have a contact form that sends the request to an email address that no human ever monitors?

I see two common issues: the website creator bit bucket, and the sales-proof company.

Website Creator Bit Bucket

This is one of my least favourite issues. Countless websites have flaws that stop you interacting with them. Often, the team that designed the site might have included a link for letting them know if you have a technical issue with the site.

So often, the team that built the site has moved on, perhaps aren't even associated with the company any more, and the output of the contact form might as well go straight in the bin. No-one is ever going to see it.

Sales-Proof Company

This is the worst of the two, and it's especially important in the current pandemic-related situation. I'll give you an example.

There's a tech company in Melbourne that I've loved to deal with in the past. Their people are knowledgeable and friendly and are just pleasant to deal with.

So when I wanted to buy another high-end NVMe drive the other day, they were the first people I checked. Their site said they had them in stock, the price was fine, and I decided to buy one from them. However, their online order entry application would not allow me to enter the correct billing address for my credit card, based on silly rules. (I'll write more about that another day).

Bottom line is that I couldn't complete the order. If that's happened in the past, I've called them, and they've processed it over the phone. But with COVID-19 happening, they haven't managed to get a good system to divert calls to their own sales people working from home. So they just have a note saying their phones are temporarily not being answered. (Mistake #1)

I had no choice but to use their "Sales Enquiry" contact form.

The Wait

I waited, and waited and heard nothing. (Mistake #2)

After two days, I ordered the drive from another supplier, and it arrived quickly.

Nearly three weeks after I filled in their contact form, the first company did send me an email to check if I still needed help. But that was way, way too late.

But they did respond

On the positive side, they did at least contact me. I've read reports that say that up to 80 percent of online sales enquiry forms are never responded to at all.

Don't be one of these companies!


T-SQL 101: #69 Extracting date components in SQL Server T-SQL with DAY, MONTH, and YEAR

When you're writing T-SQL code, it's really common to need to extract components of a date, and the most common need is for year, month, and day. So T-SQL has separate functions just for that.

You can see the output from this query here:

The YEAR(), MONTH(), and DAY() functions take a date (or datetime, datetime2, smalldatetime) as input and return an integer for the year, month, or day.

You can also do this with the DATEPART() function but these are simpler and clearer when you want these main components of a date.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Qixi Festival on postage stamps

I wrote my last two Mandarin blog posts about the Qixi festival and the legend of the Cowherd and Weaver Girl. I wanted to round out that discussion today.

In the West, we have a few tales like Santa Clause, and the Easter Bunny that seem pervasive. St Valentine isn't such a big deal, even though Valentine's Day is named after him.

People who haven't studied China much usually don't understand just how embedded these tales are within society, and also in the language. I see old tales appearing as idioms that end up meaning something quite different to what they say.

One place that I've seen Qixi appearing is in postage stamps. You can see one set in the main image above.

Here's another set:

Qixi postage stamp
Awesome image from youlai18

These are more recent but note the price hasn't changed. (Unlike our local postage prices)

Qizi postage stamp
Awesome image from youlai18

Learning Mandarin

I'll write more soon on the best methods for learning Mandarin. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

SQL: How do I stop my database files going into the Users folder with localdb?

I've seen questions on the forums asking about where localdb stores its database files. Often, the question is about how to stop them going into the C:\Users folder. Clearly, that doesn't seem to be a good spot for them.

localdb stores database files in the C:\Users area by default as it's trying to find a location that the current user will have permission to write to. While I understand the rationale, that's not where most users will want those files.

The answer is to not let localdb pick where the files will go.

Instead of writing:

use a more complete command like:

While you're at it, check out the CREATE DATABASE syntax and examples for other options that might be useful to you, particularly the ones around filegrowth. (Hint: Use a fixed increment, not a percentage) Not everything will work on localdb but most will.