<< SQL Server 2021 Hosting - HostForLIFE :: Using OPENJSON Function In SQL Server | SQL Server 2021 Hosting - HostForLIFE :: Another Way To Implement The Incremental Load >>

SQL Server 2021 Hosting - HostForLIFE :: Examples Of DATE/DATETIME Conversion

April 12, 2022 09:50 by author

Peter

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!