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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 error converting varchar to datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

karrojo
Starting Member

26 Posts

Posted - 08/10/2010 :  21:44:34  Show Profile  Reply with Quote
i have this syntax:

select a.field1, a.field2, a.field3, b.field1, b.field2, b.field3
from table as a inner join table as b
on a.field1 = b.field1
where a.field2 <> b.field2

a.field2 is a varchar field with length of 23 that contains date and with diff date formats. i.e aug 10 2010 12:00am, 08/10/2010, 2010-08-10 00:00:00.000

i am searching in a.field2 the records with the example formats and want to convert it to datetime. but im getting this error msg;

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

is there a missing command that i did not use. i would greatly appreciate all your help. tnx :)

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 08/11/2010 :  06:04:08  Show Profile  Reply with Quote
why do you have several different date formats in varchar field? how come?

please post some data sample.
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 08/11/2010 :  07:03:08  Show Profile  Reply with Quote
Use the ISDATE function to make sure that the date is in valid date format then change it into date time.
like this -
This might not reach upto your requirement but you can get the idea to start.


select a.field1, a.field2, a.field3, b.field1, b.field2, b.field3
from a inner join b
on a.field1 = b.field1
where ( CASE WHEN ISDATE(b.field2) = 1 THEN CONVERT(DateTime, b.field2) ELSE 1 END )
	<> ( CASE WHEN ISDATE(a.field2) = 1 THEN CONVERT(DateTime, a.field2) ELSE 1 END )



Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER
Go to Top of Page

karrojo
Starting Member

26 Posts

Posted - 08/11/2010 :  20:59:38  Show Profile  Reply with Quote
tnx for the help... we initiated to correct format all the values in b.field2 since the field is a varchar type, we change it to our required format of mm/dd/yyyy... and i still got this error msg:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 08/12/2010 :  02:44:27  Show Profile  Reply with Quote
Are you checking the date with ISDATE function ?
Because if its not in valid sql date format then you can not change it into datetime or any other format.

Please post some sample data.


Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/12/2010 :  02:53:57  Show Profile  Reply with Quote
"we change it to our required format of mm/dd/yyyy"

Dates should be presented to SQL Server in an unambigous format. Is 01/02/2003 1st Feb? or 2nd Jan? How is SQL to know? Moreover SQL will choose based on the server's settings, and the country / language setting of the currently connected user - all of which may change over time.

Present the dates as yyyymmdd (NO hyphens!!) which SQL Server will treat as unambiguous.

If your dates are in a different format use:

SET DATEFORMAT dmy

to "set" the format for the conversion, or use CONVERT(datetime, '01/02/2003', nnn) where "nnn" defines the format type (see documentation for valid values)

Edited by - Kristen on 08/12/2010 02:55:01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/12/2010 :  14:18:07  Show Profile  Reply with Quote
quote:
Originally posted by vaibhavktiwari83

Are you checking the date with ISDATE function ?
Because if its not in valid sql date format then you can not change it into datetime or any other format.

Please post some sample data.


Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER


ISDATE is not fully reliable.
See

SELECT ISDATE('2010'),ISDATE(200901)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

karrojo
Starting Member

26 Posts

Posted - 08/12/2010 :  21:58:34  Show Profile  Reply with Quote
tnx for all your replies... we work back on the table and corrected the entries (i.e. Aug 10 2010) to mm/dd/yyyy... luckily the error was gone... tnx to all for your time... :D...
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.11 seconds. Powered By: Snitz Forums 2000