• Orlando Colamatteo (2/10/2016)


    Luis Cazares (2/10/2016)


    micang (2/10/2016)


    Orlando Colamatteo (2/9/2016)


    The performance of FORMAT is very poor when used over large sets of data which is unfortunate because the syntax is more readable than any of the other methods available for work like this. This happens to be one place where I would use FORMAT **IF** your example is representative of how you need to use it, i.e. you want to get the format of the current date into a variable and use it over and over, like this:

    DECLARE @MMDDYYYY CHAR(8) = FORMAT(GETDATE(), 'MMddyyyy');

    SELECT @MMDDYYYY AS [@MMDDYYYY]

    If your intent is to format datetime data stored in a column then do not use FORMAT, use REPLACE with CONVERT as shown in your original post.

    nevermind...just noticed you're on SQL 2008

    Thank you very much for this information, it has not gone wasted as sometime this year we will be upgrading to 2012, so this is very helpful, appreciate it.

    Thanks

    Michael

    Be careful with this, as the FORMAT function is significantly slow. People have made performance tests showing that it can be up to 40 times slower than the CONVERT function.

    No doubt. I tried to level a similar caveat as well in my post. Only use FORMAT as you would a Scalar UDF, i.e. to assign a value to a variable or for a guaranteed single row.

    Every one of those points is right on, from it working, being slow and being for SQL 2012 and above. One of these days, I'll get the time to finish up my own ITVF that does it well. I started it quite a while ago and want to encompass all the datetime parts, but other things always seem to get in the way. I have it working as a scalar, but need to finish converting it. I'll write it up if I ever get it done. My goal is to blow the doors off FORMAT and it won't be done until I do. 😛