Conversion failed when converting datetime from ch

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conversion failed when converting datetime from ch

Author

Topic

shemayb
Posting Yak Master

159 Posts

Posted - 2007-08-24 : 09:16:28

Conversion failed when converting datetime from character string:How do I trace this problem?

Funnyfrog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 09:18:42

this is a starter

select * from yourtablenamehere where isdate(yourcolumnnamehere) = 0

E 12°55'05.25"
N 56°04'39.16"

shemayb
Posting Yak Master

159 Posts

Posted - 2007-08-24 : 09:21:33

What would i do next?

Funnyfrog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 09:27:55

Did the query return any records?

E 12°55'05.25"
N 56°04'39.16"

shemayb
Posting Yak Master

159 Posts

Posted - 2007-08-24 : 09:28:58

I didn't get any results to my query.

Funnyfrog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 09:31:35

Then I assume all records can be interpreted as dates.

If you run

SELECT MIN(yourcolumnnamehere), MAX(yourcolumnnamehere)
from yourtablenamehere

what do you get?

E 12°55'05.25"
N 56°04'39.16"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-24 : 09:31:58

What string format is the date currently in (mmddyyyy?)

Jim

shemayb
Posting Yak Master

159 Posts

Posted - 2007-08-24 : 09:33:25

yeah,it is in mmddyyyy format.

Funnyfrog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 09:33:56

quote:
Originally posted by shemayb

I didn't get any results to my query.

AAARghhh!

Did you get any result when running MY query?

SELECT * FROM YourTableNameHere WHERE ISDATE(YourColumnNameHere) = 0

E 12°55'05.25"
N 56°04'39.16"

shemayb
Posting Yak Master

159 Posts

Posted - 2007-08-24 : 09:35:16

SELECT MIN(yourcolumnnamehere), MAX(yourcolumnnamehere)
from yourtablenamehere:When i do this i get the min date and the max date.

Funnyfrog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 09:35:17

SELECT CAST(STUFF(STUFF(YourColumnNameHere, 4, 0, '/'), 2, 0, '/') AS DATETIME)
FROM YourTableNameHere

E 12°55'05.25"
N 56°04'39.16"

shemayb
Posting Yak Master

159 Posts

Posted - 2007-08-24 : 09:36:33

i didn't get any results when running your query

Funnyfrog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 09:53:08

quote:
Originally posted by shemayb

yeah,it is in mmddyyyy format.

Funnyfrog

So you didnt use DATETIME datatype?

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-24 : 09:53:08

If you're in mmddyyy try Peso's solution like this
SELECT CAST(STUFF(STUFF('08302007', 5, 0, '/'), 3, 0, '/') as DATETIME)

Jim

shemayb
Posting Yak Master

159 Posts

Posted - 2007-08-24 : 09:55:26

my datattype is datetime

Funnyfrog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 09:56:34

quote:
Originally posted by shemayb

Conversion failed when converting datetime from character string:How do I trace this problem?

Funnyfrog

Show the query that caused the error

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-05 : 17:35:53

tells me you are passing a value that looks like a date but when you try to insert it into a table or have a variable of datetime receive it you get the error message?

post the query and the parameter values so it's sql language (i sense communication problem)

--------------------
keeping it simple...

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-05 : 21:02:14

Your datetype is datetime?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 02:45:42

quote:
Originally posted by Koji Matsumura

Your datetype is datetime?

Until the OP posts full query, we cant assume anything

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 02:47:21

quote:
Originally posted by Peso

this is a starter

select * from yourtablenamehere where isdate(yourcolumnnamehere) = 0

E 12°55'05.25"
N 56°04'39.16"

Like ISNUMERIC(),ISDATE() should be dealt with careful as it can treat integer values as dates

eg

SELECT ISDATE(2000),ISDATE('2000')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources