SQL SERVER – Convert Formatted Integer Values into Date

One of my clients of  Comprehensive Database Performance Health Check asked me a question about how to Convert Formatted Integer Values into Date. Let us read his question first:

“Pinal, I have a table with one of the columns stores date values in integer value (ddMMyyyy format). How to convert it to proper Date value?”

Well. There can be several methods we can use. Two simple methods are explained here.

SQL SERVER - Convert Formatted Integer Values into Date ConvertFormatted-800x154

Let us create a variable with INT datatype and assign 09-Dec-2020 as integer format (ddMMyyyy).

DECLARE @DATE INT
SET @DATE=09122020

Now we can use either of the following methods to convert formatted integer value into a valid date.

SELECT CONVERT(DATE,CONCAT(@DATE/1000000,'/',@DATE/10000%100,'/',@DATE%10000),103) AS DATE_VALUE

SQL SERVER - Convert Formatted Integer Values into Date dateparts

The method will convert the integer value into dd/MM/yyyy format by extracting dd, MM, and yyyy parts separately using arithmetic operation and add / between MM and yyyy parts. It is then converted into VARCHAR datatype. CONVERT function with style 103 is used to convert the formatted string into a proper date value.

Let me know if you know any other simple method, I will be happy to post them on this blog with due credit to you. Meanwhile, you can connect with me on Twitter or LinkedIn. Once again thanks for reading this blog post.

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

, ,
Previous Post
SQL SERVER – Change Database and Table Collation
Next Post
SQL SERVER – Stored Procedure Parameters and Single Quotes

Related Posts

3 Comments. Leave new

  • Michael D Ballard
    December 9, 2020 7:22 am

    You’re working to hard:
    DECLARE @iDate INT = 31122020;
    SELECT CONVERT(DATE, STUFF(STUFF(CONVERT(CHAR(8), @iDate), 5, 0, ‘/’), 3, 0, ‘/’), 103)

    Reply
  • richard whight
    December 9, 2020 7:41 am

    I might be missing something but this is essentially yours anyway but just without having to add in the slashes .

    DECLARE @DATE INT
    SET @DATE=09122020

    SELECT DATEFROMPARTS(@DATE%10000 , @DATE/10000%100, @DATE/1000000)

    Reply
  • Carter Cordingley (@CarterCordingl1)
    December 14, 2020 7:31 am

    Select Cast(Format(09122020,’##-##-####’) as Date)

    Reply

Leave a Reply

Menu