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.
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
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)
3 Comments. Leave new
You’re working to hard:
DECLARE @iDate INT = 31122020;
SELECT CONVERT(DATE, STUFF(STUFF(CONVERT(CHAR(8), @iDate), 5, 0, ‘/’), 3, 0, ‘/’), 103)
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)
Select Cast(Format(09122020,’##-##-####’) as Date)