CODEWITHSUNDEEP
sqlsql-server
JAck28
JAck28

Reputation: 907

T-SQL Conversion failed when converting date and/or time from character string only on View

I'm trying to use a T-SQL query to get data from the past week. I have view which returns the following columns:

Name (varchar) 
Date Registered (DateTime)

I am trying to use the view to only get the last weeks data. However, I keep getting the error:

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

Here's my sql:

DECLARE @Now DATETIME = GETDATE();
DECLARE @6DaysAgo DATETIME = DATEADD(day,-6,@Now);

SELECT * 
FROM vw_Logs l
WHERE convert(DATETIME, 'l.Date Registered', 121) BETWEEN @6DaysAgo and @Now

When I write the exact same SQL statement, using the table instead of the view, I do not get an error.

DECLARE @Now DATETIME = GETDATE();
DECLARE @6DaysAgo DATETIME = DATEADD(day,-6,@Now);

SELECT * 
FROM tbl_Logs l
WHERE convert(DATETIME, l.DateRegistered, 121) BETWEEN @6DaysAgo and @Now

I suspect it has something to do with the column names? The view has a space (Date Registered) and the table does not (DateRegistered).

Is this the case? If so, how can I get the last weeks data from the view w/o changing the column name?

Upvotes: 1

Views: 4571

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

I see a couple of issues here. First, why do you convert DateRegistered to a datetime? You stated it is already a datetime column right? Formatting it isn't going to make any difference when doing a comparison. It does however render your query nonSARGable and any indexes are that column are now ignored.

The second issue I see is that I am guessing you want anything within the last 6 days. The way you have this coded it is not accurate. Your calculation will exclude any rows that happened on that day before the current time of day.

Look at the values of your variables.

DECLARE @Now DATETIME = GETDATE();
DECLARE @6DaysAgo DATETIME = DATEADD(day,-6,@Now);

select @Now, @6DaysAgo

Instead you could do something like this. It is SARGable and it does not have the issue of the timestamp.

SELECT [Column List]
FROM vw_Logs l 
WHERE l.[Date Registered] > dateadd(day, datediff(day, 0, getdate()) - 6, 0) 
and l.[Date Registered] < GETDATE()

Upvotes: 1

marc_s
marc_s

Reputation: 754688

It's not the exact same SQL statement! You need to enclose your column name with a space in it in your view in square brackets - not single quotes:

SELECT * 
FROM vw_Logs l
WHERE CONVERT(DATETIME, l.[Date Registered], 121) BETWEEN @6DaysAgo AND @Now

Putting that expression in single quotes makes it a string literal, and a string literal which really doesn't convert well into a DATETIME ....

Upvotes: 3

Related Questions