Introduction
I've noticed a bit of confusion regarding date conversion in T-SQL; recurring questions on how to strip the TIME part from a DATETIME variable or 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 it the value "2015-05-15T18:30:00.340".
With the first SELECT, we 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 add a TIME type variable in the second case.
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 maintain a DATETIME, resetting (or setting to zero) its TIME part, we could use a double casting, as seen 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 variable type to its original one. But since the TIME part is 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 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 returns 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 an output like the following:
![]()
Summary
Executing the code, we will print each CONVERT style representing our date. I hope this helps! A quick reference to spot what we need in a specific context.