CODEWITHSUNDEEP
sql-serverdatetime
skyline01
skyline01

Reputation: 2101

GETDATE() returning wrong datetime format

I am working in SQL Server 2012. I have a table that has a date column (Some_Date) which, unfortunately, is created as an int. (Why is a whole other matter.) I am trying to compare it to the result of DATEADD using GETDATE(). So, for example, I have:

SELECT TOP 1
CONVERT(datetime, CONVERT(varchar(8), Some_Date), 112)
FROM dbo.some_table

This query returns Some_Date values in the format I want, which is YYYY-MM-DD HH:MM:SS.SSS. The variable that I am trying to compare it to is:

DECLARE @refdate datetime;
SET @refdate = DATEADD(month, -3, GETDATE());

When I use @refdate in the query above, SQL is throwing a conversion error. So, I checked the format of @refdate using PRINT(@refdate). It is displaying the following value:

Dec 30, 2015 5:00PM

I need to get this variable into the same format as the converted format of SOME_DATE. How can I change the format of datetime variables to be in the desired format?

Update: The query that is throwing the error is:

DECLARE @refdate datetime;
SET @refdate = DATEADD(month, -3, GETDATE());
SELECT TOP 1
Some_Date
FROM dbo.some_table
WHERE
CONVERT(datetime, CONVERT(varchar(8), Some_Date), 112) >= @refdate

Upvotes: 0

Views: 1987

Answers (1)

Ivan Gritsenko
Ivan Gritsenko

Reputation: 4236

CONVERT(datetime, CONVERT(varchar(8), Some_Date), 112) this what causes the error.

I think you have some invalid values in Some_Date column and that's all. It can be a value with more or less than 8 digits. It has nothing to do with datetime formats. Check it with this query:

select Count(*)
from dbo.some_table
where LEN(CONVERT(varchar(8), Some_Date)) <> 8

Upvotes: 1

Related Questions