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

Question

SELECT * ,[Due]
  FROM [Events]

 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? Can someone show me how to cast or convert this so that it will work?  Noob here.

Answer 1

What is the type of the Due column?

Also, when dealing with datetime values use DATEADD function

where Due >= dateadd(day, 90, CAST(CURRENT_TIMESTAMP as Date))

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 2

Answer 3

Learn to store date/datetime data as they are, not as a string type anot not to use the simple + number to a datetime. It will not work with date data type.

Try:

 Where Cast(Due as datetime) >= Dateadd(day, 90,getdate() )

Answer 4

Same error

Answer 5

--select try_parse('1/1/2015' as datetime)
--, try_convert(datetime, '1/1/2015' )


Where try_parse(Due as datetime) >= Dateadd(day, 90,getdate() )

You have invalid datetime data in the column.

You can find them by using:

select * from Events Where try_parse(Due as datetime) is null

Answer 6

Well, that's the problem. You should not store dates as characters.

If you're in charge of that table, I suggest to fix the type of the Due column to be either date or datetime (if you don't need time portion, use date type).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 7

Can you post some sample data from Due field? Is the date format consistent in it?

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 8

Thank you for all the help.  I am not sure why this database did not use a standard date column here.  Nothing is easy I guess.

I tried

SELECT [Due]
  FROM [Events] (nolock)

Where try_parse(Due as datetime) >= Dateadd(day, 90,getdate() )

but got:

Msg 195, Level 15, State 10, Line 5
'try_parse' is not a recognized built-in function name.

Answer 9

I also tried

SELECT [Due]
  FROM [Events] (nolock)

Where Due = 'Null'

and no results were returned.

Answer 10

I also tried

SELECT [Due]
  FROM [Events] (nolock)

Where Due = 'Null'

and no results were returned.

it should be

SELECT [Due]
  FROM [Events] (nolock)

Where Due IS Null

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 11

Thank you for all the help.  I am not sure why this database did not use a standard date column here.  Nothing is easy I guess.

I tried

SELECT [Due]
  FROM [Events] (nolock)

Where try_parse(Due as datetime) >= Dateadd(day, 90,getdate() )

but got:

Msg 195, Level 15, State 10, Line 5
'try_parse' is not a recognized built-in function name.

try_parse will work only from sql 2012 onwards

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 12

Check in what format date values are present in the field

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 13

Answer 14

Looks like version 2008 R2.  Also, this is a vendor provided database. 

Answer 15

SELECT [Due]
  FROM [Events] (nolock)

Where Due IS Null

Returned no results

Answer 16

You can use the IsDate function.  It is not as useful as Try_Parse, but it does work on earlier versions of SQL Server.  Try using

SELECT [Due]
  FROM [Events]

Where Cast(Case When IsDate(Due) = 1 Then Due Else NULL End as datetime) >= Dateadd(day, 90,getdate() )

to run your query.

If you want to find all the rows where Due is not a valid date, Try

SELECT *
  FROM [Events]
Where IsDate(Due) = 0

Tom

Answer 17

Try:

Where IsDate(Due)=0

Answer 18

Try

declare @t table(id int identity(1,1) primary key, due char(10))
insert into @t (due) values ('2015/02/26'), ('2015/02/29'), ('2017/03/14')

select * from @t where 1 = case when isdate(due) = 1 then case when due >= dateadd(day,90, cast(current_timestamp as date)) then 1 else 0 end
else 0 end

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 19

Running the declare statement only returned 1 result. I admit this query is beyond me but I was expecting more than 1 or everything from today out 90 days.

Answer 20

Thanks. Running the cast statement at least go me passed that conversion error. It is showing dates a lot farther out than 90 days. I can try to play around with it some now that it is at least returning results.

Answer 21

I was showing you the problem and a possible solution. For your real problem you don't need declare, you only need the where condition I showed applied to your actual table Events.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 22

Do you want to actually find events that are due in next 90 days? If yes, your where condition should check for dates between

cast(current_timestamp as date) and dateadd(day, 90, cast(current_timestamp as date))

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 23

     i your column have more 20 rows  in one date and time 

    before without max you cann't select any rows 

   in any time you need use select with max() 

  

create table #events (due char(20))

insert into #events values('2015/02/26')
go 20

with x
as
(select max(due) as md from #events)
select *from x where cast(x.md as datetime) >= Dateadd(day, -270,getdate() )

drop table #events


md
2015/02/26          

for example i was use in this query -270 day for see results

or for another situation

i think in your table have dublicate rows

Answer 24

By using Naomi's code your query should be

SELECT
	 * 
	,[Due]
FROM 
	[Events]
Where 
	1 = (case when isdate(due) = 1 then 
			case when due between cast(current_timestamp as date) and dateadd(day, 90, cast(current_timestamp as date)) then 1 
			else 0 end
	     else 0 end)

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you