CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
TroySteven
TroySteven

Reputation: 5157

Converting Varchar to Datetime on SQL Server

SELECT SubmitDate, Type, Location, CONVERT(DATETIME, SubmitDate, 101) AS DDate
FROM Employee
WHERE (Type IN ('C', 'Q')) 
  AND (DDate > DATEADD(DDate, - 1, GETDATE()))

I need to get all records from the past year, SubmitDate is a varchar. I use CONVERT to convert the column to datetime. However I am getting the following error:

DDate Not recognizable dateadd option.

I tried switching the CONVERT to the following and it didn't work: 

CONVERT(DATE, SubmitDate, 101)

Upvotes: 0

Views: 214

Answers (1)

PausePause
PausePause

Reputation: 806

As users have commented above, you cannot reference column aliases in WHERE clauses on your query. You can, however, use the column transformation in the WHERE clause. It isn't very efficient, but if you're using the query once or twice (IE: Not in a SSIS package or automated task) it should be alright.

SELECT SubmitDate, Type, Location,CONVERT(datetime, SubmitDate, 101) AS DDate
FROM Employee
WHERE ([Type] IN ('C', 'Q')) 
AND (CONVERT(datetime, SubmitDate, 101) > DATEADD(Day, - 1, GETDATE()))

The second problem with your query is the DATEADD clause. DATEADD requires three paramaters: The datepart, the number, and the date. You have an invalid datepart, or "what part of the date are you performing the arithmetic on". DDate is not a datepart, it's a column. A datepart is values like Month, Day, Year, Week, etc.
I edited the query to set the datepart to "Day" so the clause will evaluate (using pseduocode) GETDATE()-1 Day, but you can set it to GETDATE()-1 Month, or hour. But the current psedocode expression of GETDATE()-1 DDate doesn't make sense to your SQL Server client

Refer to Microsoft documentation for more DATEADD information

Upvotes: 4

Related Questions