error converting varchar to datetime

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

SQL Server 2000 Forums

Transact-SQL (2000)

error converting varchar to datetime

Author

Topic

karrojo
Starting Member

26 Posts

Posted - 2010-08-10 : 21:44:34

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

746 Posts

Posted - 2010-08-11 : 06:04:08

why do you have several different date formats in varchar field? how come?

please post some data sample.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-11 : 07:03:08

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

karrojo
Starting Member

26 Posts

Posted - 2010-08-11 : 20:59:38

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.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-12 : 02:44:27

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

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 02:53:57

"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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 14:18:07

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/

karrojo
Starting Member

26 Posts

Posted - 2010-08-12 : 21:58:34

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...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources