hosting tips

SQL Server Tutorial: How to Conversion Of DATE/DATETIME ?

Suzan Kara

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:


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.

SQL Server Hosting Recommendation

HostForLIFEASP.NET is a large provider that serves millions of websites and is constantly growing.In fact, the fast speed of HostForLIFEASP.NET is not only achieved by their great web servers and advanced data center infrastructures, but also by some special technologies and services that are listed in the following.

HostForLIFEASP.NET Network Operations Center (NOC) is built right in the center of their support facilities and is staffed 24/7/365 by certified systems administrators and network engineers. The 21 screens that make up their NOC monitor everything from network traffic and performance to power, temperature and security systems to services, applications, known vulnerabilities and RAID array status on customer servers.

HostForLIFEASP.NET data centers are built upon a unique pod design concept, making them functionally independent with distinct and redundant resources, and fully integrated through their revolutionary network architecture. You can have direct control over your system in any data center and full access to all of their back-end services—all fully automated and on demand.

hostforlifebanner