| Author |
 Topic  |
|
|
shemayb
Posting Yak Master
Philippines
159 Posts |
 Posted - 08/24/2007 : 09:16:28
|
Conversion failed when converting datetime from character string:How do I trace this problem?
Funnyfrog |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/24/2007 : 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
Philippines
159 Posts |
Posted - 08/24/2007 : 09:21:33
|
What would i do next?
Funnyfrog |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/24/2007 : 09:27:55
|
Did the query return any records?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
shemayb
Posting Yak Master
Philippines
159 Posts |
Posted - 08/24/2007 : 09:28:58
|
I didn't get any results to my query.
Funnyfrog |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/24/2007 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 08/24/2007 : 09:31:58
|
What string format is the date currently in (mmddyyyy?)
Jim |
|
|
|
shemayb
Posting Yak Master
Philippines
159 Posts |
Posted - 08/24/2007 : 09:33:25
|
yeah,it is in mmddyyyy format.
Funnyfrog |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/24/2007 : 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
Philippines
159 Posts |
Posted - 08/24/2007 : 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
Sweden
29156 Posts |
Posted - 08/24/2007 : 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
Philippines
159 Posts |
Posted - 08/24/2007 : 09:36:33
|
i didn't get any results when running your query
Funnyfrog |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 08/24/2007 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 08/24/2007 : 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
Philippines
159 Posts |
Posted - 08/24/2007 : 09:55:26
|
my datattype is datetime
Funnyfrog |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 08/24/2007 : 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
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 09/05/2007 : 17:35:53
|
my  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 - 09/05/2007 : 21:02:14
|
| Your datetype is datetime? |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 09/06/2007 : 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
India
22469 Posts |
Posted - 09/06/2007 : 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 |
|
|
| |
Topic |
|
Conversion failed when converting datetime from ch
