SQL SERVER – PRINT Statement and Format of Date Datatype

I have said many times that I love my job because every single day I find something new to learn and explore. Recently while working on Comprehensive Database Performance Health Check, we took a 5 minutes coffee break and during that short time, one of the DBA asked me a question about PRINT Statement and DataType.

Here is the question in his exact words – “How does PRINT statement format the date values by default?“At first, I found the question a bit difficult to understand but later on, I understood that DBA wants to know how it will be formatted when we use it with a PRINT statement compared to SELECT statement.

Let us explore this more in details with the following example:

SELECT Statement

DECLARE @DATE DATETIME
SET @DATE ='2019-01-18 20:23:10'
SELECT @DATE AS DATE_VALUE

When you execute the above statement, The result is

DATE_VALUE
————————
2019-01-18 20:23:10.000

Pay attention to the format of the above date value.

Now execute the same statement with PRINT instead of a SELECT statement.

PRINT Statement

DECLARE @DATE DATETIME
SET @DATE ='2019-01-18 20:23:10'
PRINT @DATE

The result is

Jan 18 2019 8:23PM

It is formatted differently. It is because the return data type of the PRINT statement is either VARCHAR(8000) or NVARCHAR(4000).

CAST, Date and Format

It is not necessary that only a print statement gives us the date in such format. If you use CAST function over the date datatype and convert it to VARCHAR, you will see the same format for the date datatype as well.

Here is the example of the same –

DECLARE @DATE DATETIME
SET @DATE ='2019-01-18 20:23:10'
SELECT CAST(@DATE AS VARCHAR(30)) AS DATE_VALUE

The result is

DATE_VALUE
————————
Jan 18 2019 8:23PM

Not everything in SQL Server is complicated or difficult. There are so many little fun elements to learn that I believe I will never run out of the topics to write about on this blog post.

If you have similar learning nuggets, please do share with me and I will blog about them with due credit to you. Looking forward to your contribution.

Reference: Pinal Dave (https://blog.sqlauthority.com)