In our last time together, we were talking about Doing more when trying to Convert. One of the many things I mentioned was using the CONVERT function to change a date to always look a particular way – for example, MM-DD-YYYY or DD/MM/YYYY.
Since the CONVERT function requires you to either look up each time the corresponding number to the format you want – we all know there has to be an easier way.
This time, I’d like to introduce you to the FORMAT function which came out on SQL Server 2012. It has two uses when it comes to dates:
The first one makes it very easy to work with dates.
You will just do the following:
SELECT FORMAT(TheDateField, 'yyyy-MM-dd') FROM dbo.Table1 And you’re off to the races!
Now, there are two big things to remember about the FORMAT function.
- You will have to be working with a field that is of data type date. If not, you will need to convert it to date to use the FORMAT function.
For example,
Notice the TRY_CONVERT function used in this case because of all of those nasty “not-so-datey dates.” I show it in this query so that everyone can see that there are no tricks up my sleeve.
2. You will need to look up, remember, or even figure out each time what the correct capitalization for the letters is. Notice that the month, in the query, is capitalized, but the day and year are not. It truly has to be that way or you will get minutes if you use lower case m’s. Or complete wrong thing if you capitalize everything.
Below, please find the correct letters and capitalization for dates:
- dd – this is day of month from 01-31
- dddd – this is the day spelled out
- MM – this is the month number from 01-12
- MMM – month name abbreviated
- MMMM – this is the month spelled out
- yy – this is the year with two digits
- yyyy – this is the year with four digits
- hh – this is the hour from 01-12
- HH – this is the hour from 00-23
- mm – this is the minute from 00-59
- ss – this is the second from 00-59
- tt – this shows either AM or PM
- d – this is day of month from 1-31 (if this is used on its own it will display the entire date)
Next time, we’ll learn even more about the FORMAT function. Until next time my friends
