question

The "correct" answer is that the first query does no formatting other than what SQL Server thinks it should look like. It only drops the time element because the DATE datatype doesn't have a time element. The output in the GRID mode can sometimes be different than when in the TEXT mode (not in this case, though, and I believe they fixed that problem in 2008).. For a return to other code that consumes the information, it will be perceived as a DATE datatype. The second query formats the date into the format identified by the string literal but that's not the end of it. The FORMAT function is a pig when it comes to performance and is (I measured it) 44 times slower than CONVERT. If it's passed to other consuming code, it will be perceived as character based data and, possibly, cause implicit conversion problems if involved in a JOIN. As a bit of a sidebar, if anyone uses FORMAT for the sake of "portability", remember that nothing is truly portable. True Portability is a myth. To answer the question of "what is the difference in output", you have two pieces of code that work. Run the bloody things and see. If this is on a test or for some homework, then you need to buckle down, try the code, or study what you're instructor has told you to study. If it's for an interview, you probably won't get that particular job and need to buckle down, try the code, and study the SQL Server documentation so you can answer that and similar questions on your next interview. Also, because the word "GO", which is a batch separator, isn't on a separate line, neither will actually produce the expected output. Instead, you'll get the following error. > Msg 102, Level 15, State 1, Line 1 > > Incorrect syntax near 'GO'