CAST and CONVERT can both be used to switch a value to a new data type. They are similar, but certainly not identical. While CAST is considered ANSI SQL and will get you across the finish line, CONVERT can give you more flexibility when it comes to formatting date values. Let’s look at an example comparing the usage of CAST and CONVERT with dates.
Most of the time, I’ll use
CONVERT(), not so much because the former is ANSI compliant, but rather because I think it’s more intuitive to remember. Date formatting is one of the few occasions in which I usually prefer
CONVERT() and that’s precisely because of the format options. Of course, if you want more custom formatting options, you can use
FORMAT(), though that function uses .NET in the background and is remarkably slow. It’s fine if you’re formatting a few dates, but if you’re outputting millions of rows, you will certainly see a marked difference.