How to convert a text DDMMMYYY date in SQL

As much as I love Excel there are some circumstances where it just doesn't cut it - more than a millions rows and computer says no; some rediculouse vlookup and you could be there for hours. So step in SQL, it can do those aforementioned tasks in a matter of seconds.

There are a couple of occasions though where SQL is not king, one of these is it's cleverness at recognising dates especially with the good old British notations.

Want to import a date in the format 'DDMMYYYY' and you might come up a bit short as annoyingly this date format isn't in the convert functions pre-defined list. You could use some Excel stylee of MID() or SUBSRTING(), but here's a quicker method - stick a few spaces in it and pass it off as 'DD MMMM YY' and you are quids in:

convert(date, stuff(stuff([txt_dte_1],6,0,' '),3,0,' '), 113)

You can check out Stuff() here.