A recent blog was extolling the advantages of being able to use the CLR routines in SQL Server 2005 to do Microsoft-style date formats, instead of having the fuss and bother of remembering the CAST or CONVERT styles for the various representations of dates<\/p>\n
It is actually quite easy to do these date formats in SQL 2000 as well, with a function. The advantage of using a function is that you can add your own favourite date formats in to taste. If your programmers are PHP programmers rather than ‘softies, you can even change it for the PHP date formatting convention.<\/p>\n
Here is the straight-forward function
I was going to add a few esoteric bits and pieces such as the obvious one of being able to add the ‘st’, ‘nd’, ‘rd’, and ‘th’ suffix (as in ’21st March 2006′) bit the function was beginning to get a bit long for a blog. The reason the function was getting long was that I use the built-in date formats wherever possible before iterating through the format string only to do a ‘special’.<\/p>\n
There is more optimisation to be had, possibly at the expense of the clarity of the code.I’d already written it when I realised that there should be just one loop, with the standards peeling off at the first iteration. It maybe should be done in a table rather than using all those case statements but I doubt that one gains a great deal in performance. I’d be interested to hear from anyone who spots a bug or can suggest an improvement. I’d love to do an extention that represented the time the way one speaks it in english, with ‘Ten to one’ or ‘quarter to four’, ‘half past six’ and so on, but that will have to wait for another occasion.<\/p>\n
<\/pre>\n","protected":false},"excerpt":{"rendered":"A recent blog was extolling the advantages of being able to use the CLR routines in SQL Server 2005 to do Microsoft-style date formats, instead of having the fuss and bother of remembering the CAST or CONVERT styles for the various representations of dates It is actually quite easy to do these date formats in SQL……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-2284","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2284","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2284"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2284\/revisions"}],"predecessor-version":[{"id":41370,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2284\/revisions\/41370"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2284"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}