CODEWITHSUNDEEP
sql-servert-sqldatetime
user12235434
user12235434

Reputation:

Convert Int data type to date

The SQL server database I'm using has a field stored as an integer but it is a date. Currently it is showing 20191012. I've tried the below and it seems to convert it but I'd like to show it in 10/12/2019 format. Thanks.

CONVERT(DATE,CONVERT(VARCHAR(20),EFFDAT)) AS DATE_EFF

Upvotes: 1

Views: 1414

Answers (2)

Zorkolot
Zorkolot

Reputation: 2017

DECLARE @myint int = 20191012

SELECT CAST(CAST(@myint as char(8)) as date) AS [theDate]
SELECT CONVERT(char(10), (CAST(CAST(@myint as char(10)) as date)), 101) AS [theDateStyleString]

Is one way, where the first query simply casts the data to the date datatype and the second query additionally converts to a string with the MM/DD/YYYY format. But understand that converting to a string is not the same as keeping the date datatype- since date functions and comparisons (>, <, etc) do not work correctly on strings.

This produces output:

theDate
----------
2019-10-12


theDateStyleString
------------
10/12/2019

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

You first need to convert the number 20191012 to a date:

SELECT DATEFROMPARTS(
           20191012 / 10000,
           20191012 % 10000 / 100,
           20191012 % 100
       )
-- 2019-10-12 (DATE)

Then FORMAT it:

SELECT FORMAT(DATEFROMPARTS(
           20191012 / 10000,
           20191012 % 10000 / 100,
           20191012 % 100
       ), 'MM/dd/yyyy')
-- 10/12/2019 (NVARCHAR)

The easier way is to convert the number to a string and do a couple of SUBSTRING()s.

Upvotes: 1

Related Questions