4

I have Date(2017-01-07) and Time(23:58:06 PM) Two Column, i have tried the below Query 

SELECT * FROM Report WHERE Time > DATEADD(HOUR, -2, GETDATE())

Time is NVARCHAR, DATE is Date

But i am getting the Error Called "Conversion failed when converting date and/or time from character string."

Please Help me to get this solved!!! i am a SQL Starter

T.Anand
  • 463
  • 2
  • 6
  • 19

2 Answers2

3

It seems that Time is a TEXT field.

You should consider to combine Date and Time to get a DateTime field, and then use it in the WHERE clause.

This answer in SO could help: https://stackoverflow.com/a/7289875/3270427

SELECT * 
FROM Report 
WHERE CAST(Date AS DATETIME) + CAST(Time AS DATETIME) >= DATEADD(HOUR, -2, GETDATE());
Community
  • 1
  • 1
McNets
  • 10,352
  • 3
  • 32
  • 61
1

What you want to do is add them together and then compare to get date. Unfortunately, you cannot add a date and time together. Happily, you can add a datetime and time together.

So:

WHERE (CAST(date as datetime) + time) >= DATEADD(HOUR, -2, GETDATE())

If time is character, then you should be able to convert that as well:

WHERE (CAST(date as datetime) + CAST(time as time)) >= DATEADD(HOUR, -2, GETDATE())

Or, if the PM is redundantly part of the time, then:

WHERE (CAST(date as datetime) + CAST(LEFT(time, 8) as time)) >= DATEADD(HOUR, -2, GETDATE())

Unfortunately, this will not use an index. But, one simple method is to use a computed column and an index:

alter table report add datetimecol as (CAST(date as datetime) + time);
create index idx_report_datetimecol on report(datetimecol);

EDIT:

You seem to have a problem with your data. Try:

select time
from report
where try_convert(time, timecol) is null;

or:

select time
from report
where try_convert(time, left(timecol, 8)) is null;

If the date column is also a string (really bad idea to not use native data types), then test that as well.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for you Valuable reply, but still im getting the same error with this error(Column 'datetimecol' in table 'Report' cannot be used in an index or statistics or as a partition key because it is non-deterministic.), this table is updating every 5mins continuously using script, so what i did for time being is select top 300 rows to show the last 2hours report. – T.Anand Jan 07 '17 at 20:15
  • @T.Anand . . . Well, at least you are learning the lesson to use built-in datatypes. They are there for a reason. You now have to search for mis-structured strings. – Gordon Linoff Jan 07 '17 at 20:38