Examples Of DATE/DATETIME Conversion

Introduction

I've noticed a bit of confusion when it comes to date conversion in T-SQL; recurring questions on how to strip the TIME part from a DATETIME variable, or how to convert between locales. Here we will see a fast method to split a DATETIME from its sub-parts DATE and TIME and how to reset the TIME part in a DATETIME.

 

We'll also see a method to quickly retrieve a list of all the possible conversion formats, applied to a certain date.

Let's consider the following script: 

DECLARE @myDateTime DATETIME
SET @myDateTime = '2015-05-15T18:30:00.340'

SELECT @myDateTime

SELECT CAST(@myDateTime AS DATE)
SELECT CAST(@myDateTime AS TIME)
SELECT CAST(CAST(@myDateTime AS DATE) AS DATETIME)

I've created a DATETIME variable, named @myDateTime, and assigned to it the value "2015-05-15T18:30:00.340".

With the first SELECT, we simply print out that value.

 

But look at the three SELECTs that follow the first. We'll use the CAST function to convert between data types, asking, in the first case, to output our DATETIME as a DATE and in the second one, add a TIME type variable.

 

That will have the effect of suppressing the part of the DATETIME that we haven't asked for. Casting toward DATE will produce a variable from which the TIME part will be stripped, whereas converting towards TIME, we are asking to take away the DATE part from the DATETIME.

 

 

 

In the preceding example, we can see the result of those queries. Applying the logic seen a few lines ago, when we need to mantain a DATETIME, resetting (or setting to zero) its TIME part, we could use a double casting, as you can see in the fourth SELECT. First, we cast our DATETIME to a DATE (the internal cast of the two). That will produce a DATE-only variable. Then, with the second cast, we restore the type of the variable to its original one. But since the TIME part is now gone, the result will be in DATETIME format, with a zero TIME part.

Convert a Date in all possible formats

Sometimes we need to format a date depending on the specific locale, without remembering its conversion code. The following script will help us print all the conversion styles we can impose to a given date. It loops from a range of 0 - 255 (with many of those values not used for conversion that will be skipped thanks to the TRY/CATCH block), indicating which of those values return a valid conversion.

DECLARE @myDateTime DATETIME
SET @myDateTime = '2015-05-15T18:30:00.340'

DECLARE @index INT
SET @index = 0
WHILE @index < 255
BEGIN

   BEGIN try
      DECLARE @cDate VARCHAR(25)
      SET @cDate = CONVERT(NVARCHAR, GETDATE(), @index)
      PRINT CAST(@index AS VARCHAR) + '   ' + @cDate
   END try
   BEGIN catch
   END catch
   SET @index = @index + 1
END

We can insert an arbitrary value into the @myDateTime variable and run the script. We'll then obtain output like the following:

 

 

 

Executing the code, we will print each CONVERT style, with its representation of our date. A quick reference to spot what we need in a specific context. I hope this helps!