| Author |
 Topic  |
|
|
karrojo
Starting Member
26 Posts |
 Posted - 08/10/2010 : 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
Switzerland
746 Posts |
Posted - 08/11/2010 : 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
India
837 Posts |
Posted - 08/11/2010 : 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 - 08/11/2010 : 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
India
837 Posts |
Posted - 08/12/2010 : 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
United Kingdom
22191 Posts |
Posted - 08/12/2010 : 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) |
Edited by - Kristen on 08/12/2010 02:55:01 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 08/12/2010 : 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 - 08/12/2010 : 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... |
|
|
| |
Topic |
|
error converting varchar to datetime
