I mentioned in previous T-SQL posts that one of the challenges is that there’s no standard way to write dates, so we end up having to write them as strings. Now that was a real problem in earlier versions where people would get that wrong. The DATEFROMPARTS() function added in SQL Server 2012, though, can help with this:
This query takes the year 2019, the month 2, and the day 28 and returns a date:
2020-06-22
When writing T-SQL, it’s surprising how often you need to calculate the end of month for a given date, and so you want the last date in the month for whatever date you supply. That’s the main thing that the EOMONTH() function does.
So in this case, 16th of February 2020, we might wonder if that is a leap year. So would it be the 28th or the 29th? And so if we execute that, we find the 29th is the date, so this is actually a leap year:
2020-06-15
I’ve shown in recent posts how to extract components out of dates and times. You might also want the name of a month or a day, rather than just its number. The DATENAME() function does that.
In this query, I’ve asked for the name of the month that’s part of the date 28th February 2019. So it returns:
An important concept here though is that this is based upon my regional settings. Mine currently are set to US English, to it returned February. But if I had different regional settings, it would display the name in whatever region I was configured to use. So if I had a French configuration that would come back with Février instead.
2020-06-08
I often say that I love writing code that writes code. I use T-SQL to generate T-SQL scripts all the time. (I use Excel to do it too but that’s a story for another day). An issue I ran into a while back though, was how to output decimal numbers. I wanted to make sure I was using the correct decimal separator for the user who was running the script.
2020-06-03
I’ve seen a lot of people lately complaining about meeting burnout. It seems that in our pandemic-isolated world where staff members and others are available pretty much on call all day long, it’s become really common to have a much larger number of meetings than we used to.
I’m interested in why this is. Perhaps it’s the lack of contact leading to a desire for more contact and conversation, but if there were a lot of unproductive meetings before, nowadays that seems to have increased so much that it seems almost silly.
2020-06-02
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.
2020-06-01
I’m often working with developers who are migrating to SQL Server from Oracle. (Well, who can blame them?) One of the first questions that I’m often asked though, is where is the dual table?
In Oracle, a SELECT statement must have a FROM clause.
In SQL Server, this code will work fine:
SELECT UPPER('Hello');
It will just return the string converted to upper case. You can alias it as well:
SELECT UPPER('Hello') AS Greeting;
Again, you’ll get the converted string but the column will be called Greeting.
2020-05-29
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:
BACKUP DATABASE @DatabaseName
TO DISK = @BackupFile
WITH COMPRESSION;
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.
2020-05-28
I’ve found the sys.time_zone_info system view that was added in SQL Server 2016, really useful in a number of situations. It returns the name of each timezone, along with their current offset from UTC (like +01:00 or -07:00 ) and if they are or are not currently in daylight savings time.
However, what I’ve found myself needing to do is to convert the timezone offset part to a decimal number of hours.
2020-05-27
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.
2020-05-26