The last time we met, we discussed the glorious try_convert function and how it can save us from “not-so-datey” dates.
Most of the time, at least in a business setting, we don’t want to display NULLs to our users, so in many cases, we’re asked to put a placeholder if there is a NULL.
We start with our Nirvana Performances dataset again.
From there, we need to put something – in our case, the string “Unknown” – wherever there is a NULL. Maybe we can use a CASE statement to flip between “Unknown” and our real dates?
Sigh. Not so easy as we thought.
The problem is that we’re attempting to mix string data with date data. Obviously, the string cannot be changed into a date, but what if we change the dates into strings?
Notice that I used a CONVERT instead of a TRY_CONVERT to change the date into a varchar(10). I truly did that because every data type can be converted into varchar.
You’ll also note that there’s a new piece of goodness after the original TRY_CONVERT. We have a 101 out there for some reason…
CONVERT(VARCHAR(10), TRY_CONVERT(DATE, ConcertDate), 101)Well, that converts the original dates, originally looking like 1987-06-27, into dates like 06/27/1987. There are many formats to choose from – all found here. Feel free to find your favorite – or whatever your business partner requested – and use it as needed.
One thing to note on the link listed is that if the number is in the 100s – then you’ll have a 4 digit year. Otherwise, it’s only a 2 digit year.
Please note that when switching to other formats, you may need to change the VARCHAR(10) to something else depending upon how large your date length is. There are few things worse than getting all of your data clean just to succumb to formatting issues.
Next time, we’ll talk about different ways to show the dates. Until next time my friends!