CODEWITHSUNDEEP
sql-servercasting
user3092895
user3092895

Reputation: 11

SQL Server Converting int to Date

I have a scenario where I have an int column with the following dates for example:

20131210

20131209

What I want is, I want to convert the above to a date datatype so that I can use it with GETDATE() function. This is my try but I am getting an error:

SELECT CONVERT(DATETIME, CONVERT(varchar(8), MyColumnName))
FROM MyTable
WHERE DATEADD(day, -2, CONVERT(DATETIME, CONVERT(CHAR(8), MyColumnName))) < GetDate()

This is the error I am getting:

Conversion failed when converting date and/or time from character string.

Upvotes: 1

Views: 26357

Answers (3)

Peffa
Peffa

Reputation: 141

I've had a similar problem where I need to convert INT to DATE and needed to cater for values of 0. This case statement did the trick for me

CASE MySourceColumn
   WHEN ISDATE(CONVERT(CHAR(8),MySourceColumn)) THEN   CAST('19000101' AS DATE) 
   ELSE  CAST(CAST(MySourceColumn AS CHAR) AS DATE) 
  END
  AS MyTargetColumn

Upvotes: 0

anon
anon

Reputation:

You have at least one bad date in your column (it could be 99999999 or 20130231 or who knows). This is what happens when you choose the wrong data type. You can identify the bad row(s) using:

SELECT MyColumnName FROM dbo.MyTable
  WHERE ISDATE(CONVERT(CHAR(8), MyColumnMame)) = 0;

Then you can fix them or delete them.

Once you do that, you should fix the table. There is absolutely no upside to storing dates as integers, and a whole lot of downsides.

Once you have the date being stored in the correct format, your query is much simpler. And I highly recommend applying functions etc. to the right-hand side of the predicate as opposed to applying it to the column (this kills SQL Server's ability to make efficient use of any index on that column, which you should have if this is a common query pattern).

SELECT MyColumnName
  FROM dbo.MyTable
  WHERE MyColumnName < DATEADD(DAY, 2, GETDATE());

Upvotes: 4

Question3CPO
Question3CPO

Reputation: 1202

Try:

CREATE TABLE IntsToDates(
    Ints INT
)

INSERT INTO IntsToDates
VALUES (20131210)
    , (20131209)

SELECT CAST(CAST(Ints AS VARCHAR(12)) AS DATE)
FROM IntsToDates

Upvotes: 2

Related Questions