T-Sql 101

If you are using a string that was provided by another system, it might not be in the format that you were hoping it would be. So for example, if I have this string that says 2/29/2016, I know that’s a US date format, but if it was only 5/4/2016, there’s no way I could just easily tell that.

Conversion functions use your session settings to determine how to convert the values. When I’m converting strings to dates (and/or times), I certainly don’t want to change my settings so that I run with US configuration.

2021-01-25

In a previous post about CAST and CONVERT, I mentioned that when you use CAST, you don’t get the option to specify the format you want to use. SQL Server offers an extension to the SQL standard with CONVERT and it lets you do just that.

CONVERT has an option parameter that lets you apply a style to the data type conversion that you’re doing.

In the example shown above, I’m converting the current date and time (based upon the SYSDATETIME() function) to a varchar(8), using style 112.

2021-01-18

The rich set of data types in SQL Server and the T-SQL language are great. But sometimes, you need to change a value from one data type to another. The output from the commands above is:

CAST

ANSI SQL provided a way to do this.

CAST is the ANSI SQL way to convert from one data type to another. The syntax is basically:

CAST(ValueToConvert AS NewDataType)

And you can use it where you would have used an expression or constant.

2021-01-11

The next set of introductory T-SQL topics that I want to talk about is how to change data and/or data types. The first issue is dealing with NULL values.

Once you get your head around the idea that NULL means the lack of a value (NULL isn’t a value), you might need to replace NULL values with something else. While you could just do that with a CASE statement, T-SQL provides two built-in functions for doing this.

2021-01-04

In some upcoming posts, I’ll discuss how you can change between data types, but I wanted to show you first, how you can use the FORMAT() function to convert dates and times to strings.

This query asks for the current date and time value formatted as day then month, then (four digit) year. It also includes the desired culture (en-US) which would be used if the format required any culture-specific items, such as the names of months.

2020-07-13

I mentioned in the last T-SQL post how you could create a datetimeoffset value by combining a datetime2 with an offset. But sometimes you need to switch to a different offset. And that’s what the SWITCHOFFSET() function does:

In this query, I was taking a local time (that had a timezone offset of 11 hours), and switching it to the current time in Seattle (with a -7 hours offset):

2020-07-06

It’s great to have SQL Server data types now that handle time zone offsets. Sometimes though, you need to combine a datetime value and an offset to produce one of these new values. That’s what the TODATETIMEOFFSET() function does:

In this example, I’ve taken 28th February 2019 and added a timezone offset of 10 hours to it:

the output data type is datetimeoffset.

The data type of the first value is actually datetime2.

2020-06-29

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