conversion failed when converting date and or time from character string

Question

Hi,

I am getting the following error :

conversion failed when converting date and or time from character string

I am using Sql Server 2008.(database designed for sql 2005 later moved to sql server 2008).

Pickup_time and actual_Pickup_time are varchar(5) in database.

What is wrong with this query?


Query:

SELECT COUNT(Trip_ID) AS OntimePickupCount 
FROM MyTABLE
WHERE Start_Dt BETWEEN '01/01/2014' AND '04/30/2014'
AND (DateDiff(minute, CAST (Pickup_Time AS time), CAST (Actual_Pickup AS time )) BETWEEN 0 AND 15
OR DateDiff(minute, CAST (Actual_Pickup AS time), CAST (Pickup_Time AS time) ) BETWEEN 0 AND 15)
AND Actual_Pickup IS NOT NULL AND Actual_Dropoff IS NOT NULL

Thanks

Answer 1

What date is '04/30/2014'? It depends on culture settings:  default language for the login which executes the query, and SET DATEFORMAT value.

'04/30/2014' is an invalid date with cultures such as spanish and british. You should use independent culture date format for date literals: '2014-04-30' representing 04 april 2014.

varchar(5) for a time? It's a very bad practice to store dates or times in varchar colums.

Which data do you have in your table? Please show it.

---

"No darás tropezón ni desatino que no te haga adelantar camino" Bernardo Balbuena

Answer 2

Hi.

In this line : 'WHERE Start_Dt BETWEEN '01/01/2014' AND '04/30/2014''

You must set datetime strings acording to the language of the database, and maybe must include time.

You could try:

'WHERE Start_Dt BETWEEN '01/01/2014 00:00:00' AND '04/30/2014 00:00:00'

or

'WHERE Start_Dt BETWEEN '01/01/2014 00:00:00' AND '30/04/2014 00:00:00'

Hope this help you

Best regards

Answer 3

Well, for dates I recommend to use ISO format such as '20140430' (yyyymmdd).

Also, why do you store times in varchar(5) columns instead of time?

Try:

select cast(pickup_time as time) as Test_Time

from myTable where pickup_time IS NOT NULL

and see if you get an error.

Then try the same for the other time column. If you get errors, you have invalid data in your tables that can not be converted to time.

---

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


My blog

My TechNet articles

Answer 4

Hi,

Date in the format: 2014-04-30 00:00:00:000

time being in a varchar(5) was not in my control and it cannot be changed now. It is too complex.

With my query being same, if i use 

Start_Dt='04/30/2014'  i am getting a result it is not throwing any error.

If i change it to start_dt between '04/01/2014' and '04/30/2014' , it is throwing error.

If i look for a single date not error. Why is that happening?

Thanks

Answer 5

Naomi N ,

  I tried the query and no error .

Thanks

Answer 6

Did you try it for both times for all rows in a table (e.g. no where condition)?

Also drop for now check for IS NULL and re-try.

---

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


My blog

My TechNet articles

Answer 7

BTW, instead of the OR condition you simply may try

ABS(DateDiff(minute, CAST (Pickup_Time AS time), CAST (Actual_Pickup AS time ))) between 0 and 15 (e.g. it will not matter what happens first - pickup_time or actual_pickup_time)

---

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


My blog

My TechNet articles

Answer 8

Naomi N,

No error when i tried with  'IS NULL'. 

No records with NULL. This is a NOT NULL field in database.

Thanks

Answer 9

Once again, did you try the following query

SELECT CAST(pickup_time AS TIME) as Pickup_Time, CAST(actual_pickup_time) as Actual_Pickup_Time

FROM myTable

------------

Also, did you change your date constant formats to '20140401' and '20140430' in the main failing query. Also, minor optimization, but did you get rid of OR and used ABS function instead?

---

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


My blog

My TechNet articles

Answer 10

SELECT isdate('1900-01-01 '+pickup_time), isdate('1900-01-01 '+actual_pickup_time), *
  FROM MyTABLE
 WHERE Start_Dt BETWEEN '2014-04-01' and '2014-04-30'
and (isdate('1900-01-01 '+pickup_time) = 0 or isdate('1900-01-01 '+actual_pickup_time) = 0)

Run this, it will show you where your "bad times" are.