In previous posts, I mentioned that you can easily extract the year, month, and day from dates and times, but you might want other components. That's what the DATEPART() function does.
You can see the output from the query above here:
To use this function , we put the interval that we're after and then the value we want to find it out from. So if you look at this one, I said I wanted the day from 28th of February 2019. The day is 28.
If we look at second example, I had a date there, but I had no time shown at all, so the second comes up as zero.
In the third example, I did have seconds shown, so that was 11 hours, 59 minutes and 46 seconds. It's then extracted the 46 seconds out.
It's a useful function.
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.
Another question that I keep seeing on SQL Server forums is:
Why do my SQL Server backup files keep getting much larger?
The backup commands that the poster is using look like this:
1
2
3
BACKUPDATABASE@DatabaseName
TODISK=@BackupFile
WITHCOMPRESSION;
When you do a backup, what you're actually doing is adding a backup set to a media set. Each time you do a backup like that, you're adding another backup set to the same media set.
So, what the poster is missing is that the default for SQL Server backups, is to APPEND to the operating system file. So more and more backups are now being included in that file.
If you want the file to only include the single backup, and overwrite what's already there, you can do this instead:
1
2
3
BACKUPDATABASE@DatabaseName
TODISK=@BackupFile
WITHFORMAT,INIT,COMPRESSION;
INIT overwrites the backup file but keeps the media header. FORMAT overwrites the media header as well.
I work with many different clients and I see work that's great and I also see work that's not so great. But the work that frustrates me the most, is inconsistent work.
When we built a house a while back, there are a number of rooms that have a combination light switch like the one shown in the main image. One switch is for the light, one is for something else: most likely a fan in a bathroom, etc.
Note that the electrician who installs them doesn't label which is what I want anyway. And if they did, that would look both ugly, and wouldn't help you in the dark anyway.
What I normally see though, is that they've done it the same way in every room. I don't care if the furthest in is the light, or the closest to the door is the light. I'd prefer it was the same in every house, but in the end, either is fine.
But when only one or two is done differently to all the rest, I'm left wondering about the quality of work that the electrician did. It might seem a minor thing, but I think about how many of these he/she has installed in their career, and puzzled that they don't have a standard. Even if there's no formal standard, you'd think there'd be one in their heads.
When you're writing T-SQL code, it's also common to need to work out how far apart two dates are, or it could even be dates and times.
The DATEDIFF() function does that. In this case I'm asking how many days is it from 28th of February 2019 to 31st of July 2019? The answer is:
So it says the difference is 153 days. Now we could have used any of those other intervals for that. I mentioned them before in this post.
I could work at how many minutes that was or how many seconds that was between the two times. I could even ask how many months it is.
The first date that we pass is the from date or the from starting point. The second one is the ending time. So if I put those around the other way it will also work it out backwards, but I would see -153.
Finally, it might occur to you that if I asked for nanoseconds over a long period, a very big number would come back. It could be too big for an integer. So to get around that, there's also a DATEDIFF_BIG() function that does the same thing but returns a bigint instead.
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.
In English, most of us know the common names for parts of speech, like nouns, verbs, etc. But what are the equivalents in Mandarin?
名词 (Míngcí) is a Noun.
词 (Cí) means Word and 名 (Míng) relates to a name. That makes sense as a noun is a naming word.
词典 (Cídiǎn) is a dictionary.
动词 (Dòngcí) is a Verb. 动 (Dòng) relates to moving, so a verb is like an action word.
形容词 (Xíngróngcí) is an Adjective. 形容 (Xíngróng) basically means to describe. So an adjective is a descriptive word.
副词 (Fùcí) is an Adverb. 副 (Fù) in this case is a bit like "vice" when used for "vice captain".
代词 (Dàicí) is a Pronoun. 代 (Dài) here is a bit like "representative", which is actually translated as 代表 (Dàibiǎo). So a pronoun is like an representative word.
介词 (Jiècí) is a Preposition. 介 (Jiè) here is like "introduce", so a preposition is an introducing word.
连词 (Liáncí) is a Conjunction. 连 (Lián) relates to "connecting", so a conjunction is a connecting word.
叹词 (Tàn cí) is an Interjection. 叹 (Tàn) in this case is a bit like "sigh". It's a bit rough, but I think of an interjection as a "sigh word".
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.
I answered a forum question the other day about how to list all the columns in a SQL Server database. That's straightforward enough, but they also asked for all the extended properties for the column.
I start with the sys.columns view and join it to sys.tables and sys.schemas, to get the schema and table name. The other reason is to make sure it's not a Microsoft-supplied table. I also wanted to exclude the sysdiagrams table that is created when you first create a database diagram using SQL Server Management Studio. (Curiously, that one's not flagged as a Microsoft-supplied table).
The next join was to sys.types to get details of the data type. Notice that the join is on both system_type_id and user_type_id. You need to do that to allow for aliased data types. If you want the underlying base-type, change the ON clause to this:
1
2
ONtyp.system_type_id=c.system_type_id
ANDtyp.user_type_id=c.system_type_id
While the first query would return names like sysname, the alternative ON clause would return same column as the underlying type like nvarchar(128).
Finally, I'm outer applying a query that finds the extended properties for a column. The sys.extended_properties view provides that. It can work with many different types of objects, so you need to closely define what you're looking for. The major_id is the object (i.e. the table) and the minor_id is the column_id. I've used an OUTER APPLY to make sure the column comes back, even if it doesn't have any extended properties.
If you only want columns with extended properties, you could change the OUTER APPLY to a CROSS APPLY instead.
The DatesBetweenNoWeekends function in our free SDU Tools for developers and DBAs, is really popular. It provides a range of dates between starting and ending dates, ignoring weekends.
DatesBetweenNoWeekends is a simple table-valued function that takes two parameters:
@StartDate – the first date to return @EndDate – the last date to return
The columns returned are:
DateNumber – a sequential number for the dates returned DateValue – the date
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:
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.
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.
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.
