Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rowter
Yak Posting Veteran
76 Posts |
 Posted - 2014-05-14 : 11:23:01
|
| Hi,I am getting the following error :conversion failed when converting date and or time from character stringI 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 MyTABLEWHERE 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 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-05-14 : 11:57:22
|
| What format is the time string in? I assume its HH:MM ?? Do some rows work and some don't? What is the server local date setting? Could it be trying to convert '04/30/2014' to a date thinking the format is D/M/Y? Maybe try an ISO format instead: '20140430' |
 |
|
|
rowter
Yak Posting Veteran
76 Posts |
Posted - 2014-05-14 : 12:20:44
|
| Lamprey,I tried YYMMDD format too and it gives the same error.date is a date time in database(Date in the format: 2014-04-30 00:00:00:000)If i change the query to search for a single date, it is not throwing any error and giving me a result.Start_Dt='04/30/2014' --- No errorStart_Dt Between '04/01/2014' and '04/30/2014' throws error.Thanks |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-05-14 : 12:31:47
|
| My guess is that you have an invalid Time string in one of your time columns and the Date column is not the issue. |
|
|
|
rowter
Yak Posting Veteran
76 Posts |
Posted - 2014-05-15 : 13:51:46
|
| Lamprey,There are some junk values in the actual_pickup column which is causing this error.Thanks |
|
|
|
|
|
|
conversion failed when converting date and or time