SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Conversion failed when converting datetime from ch
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 08/24/2007 :  09:16:28  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
Conversion failed when converting datetime from character string:How do I trace this problem?

Funnyfrog

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 08/24/2007 :  09:18:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
this is a starter

select * from yourtablenamehere where isdate(yourcolumnnamehere) = 0



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 08/24/2007 :  09:21:33  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
What would i do next?

Funnyfrog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 08/24/2007 :  09:27:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Did the query return any records?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 08/24/2007 :  09:28:58  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
I didn't get any results to my query.

Funnyfrog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 08/24/2007 :  09:31:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/24/2007 :  09:31:58  Show Profile  Reply with Quote
What string format is the date currently in (mmddyyyy?)

Jim
Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 08/24/2007 :  09:33:25  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
yeah,it is in mmddyyyy format.

Funnyfrog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 08/24/2007 :  09:33:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 08/24/2007 :  09:35:16  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
SELECT MIN(yourcolumnnamehere), MAX(yourcolumnnamehere)
from yourtablenamehere:When i do this i get the min date and the max date.


Funnyfrog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 08/24/2007 :  09:35:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT CAST(STUFF(STUFF(YourColumnNameHere, 4, 0, '/'), 2, 0, '/') AS DATETIME)
FROM YourTableNameHere



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 08/24/2007 :  09:36:33  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
i didn't get any results when running your query

Funnyfrog
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 08/24/2007 :  09:53:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/24/2007 :  09:53:08  Show Profile  Reply with Quote
If you're in mmddyyy try Peso's solution like this
SELECT CAST(STUFF(STUFF('08302007', 5, 0, '/'), 3, 0, '/') as DATETIME)

Jim
Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 08/24/2007 :  09:55:26  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
my datattype is datetime

Funnyfrog
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 08/24/2007 :  09:56:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/05/2007 :  17:35:53  Show Profile  Send jen a Yahoo! Message  Reply with Quote


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...
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 09/05/2007 :  21:02:14  Show Profile  Reply with Quote
Your datetype is datetime?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/06/2007 :  02:45:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/06/2007 :  02:47:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000