Last week, we had an amazing time exploring a puzzle related to datetime in SQL Server. The response was overwhelming, with many of you requesting another puzzle to further enhance our understanding of SQL Server concepts. So, here we are with a new puzzle that focuses on date formats.
Let’s start by executing the following script:
SELECT GETDATE()When you run the above script, you will notice that it displays the current date in the format “yyyy-mm-dd”, along with the time values. The resultset will look something like this:
2016-09-19 12:32:58.737Now, let’s assume that we only want to display the date part and not the time part. There are several methods to achieve this, and you can find a relevant blog post on our website about selecting only the date part from a datetime value.
However, for the sake of this puzzle, let’s assume that we haven’t read that blog post yet. From visual inspection, it is clear that we only need the left 11 characters from the datetime value. In SQL Server, we have the LEFT() function, which returns a specified number of characters from the left side of a string.
Let’s apply the LEFT() function to our datetime value and retrieve only the date part:
SELECT LEFT(GETDATE(), 11)When you execute the above script, it does give us the date part, but the format of the displayed date changes from “yyyy-mm-dd” to “mmm dd yyyy”. The resultset will look like this:
Sep 19 2016Now, the puzzling question is: Why did the displayed date format change from “yyyy-mm-dd” to “mmm dd yyyy”?
I invite you to leave your answer in the comments section below. After 2 days, I will publish all the comments and reveal the correct answer. If you already know the answer, I encourage you to ask this question to your friends and see if they can solve the puzzle as well.
Stay tuned for the solution and more exciting puzzles in the future!