Convert from Varchar to datetime

I have imported a text file and one of my columns has time stamped dates in
there.
eg. 10/11/2004 09:55:28
I want to convert this to a datetime field so that I can query the field
more efficiently.
When I tried to alter the populated table I receive the warning, data may be
lost converting column x from varchar(50). So I press yes to continue and
get this error.
unable to modify table
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The conversion
of a char data type to a date time data type resulted in an out of range
datetime value.
... the statement has been terminated.
I am only new with to this stuff so I sure is relatively easy.
Thanks in advance.
TimHi
CREATE TABLE #Test
(
col DATETIME NOT NULL
)
INSERT INTO #Test VALUES ('10/11/2004 09:55:28')
GO
ALTER TABLE #Test ALTER COLUMN col DATETIME
SELECT col FROM #Test
--Or
SELECT CONVERT(DATETIME,'10/11/2004 09:55:28',120)
"Timmeah" <timoth@.optushome.com.au> wrote in message
news:42906de4$0$8119$afc38c87@.news.optusnet.com.au...
> I have imported a text file and one of my columns has time stamped dates
in
> there.
> eg. 10/11/2004 09:55:28
> I want to convert this to a datetime field so that I can query the field
> more efficiently.
> When I tried to alter the populated table I receive the warning, data may
be
> lost converting column x from varchar(50). So I press yes to continue and
> get this error.
> unable to modify table
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The conversion
> of a char data type to a date time data type resulted in an out of range
> datetime value.
> ... the statement has been terminated.
> I am only new with to this stuff so I sure is relatively easy.
> Thanks in advance.
> Tim
>|||> I am only new with to this stuff so I sure is relatively easy.
A good start is to understand how the datetime datatype work in SQL Server.
I suggest you start by
reading this:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Timmeah" <timoth@.optushome.com.au> wrote in message
news:42906de4$0$8119$afc38c87@.news.optusnet.com.au...
>I have imported a text file and one of my columns has time stamped dates in
> there.
> eg. 10/11/2004 09:55:28
> I want to convert this to a datetime field so that I can query the field
> more efficiently.
> When I tried to alter the populated table I receive the warning, data may
be
> lost converting column x from varchar(50). So I press yes to continue and
> get this error.
> unable to modify table
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The conversion
> of a char data type to a date time data type resulted in an out of range
> datetime value.
> ... the statement has been terminated.
> I am only new with to this stuff so I sure is relatively easy.
> Thanks in advance.
> Tim
>