Converting varchar to datetime - Prabh

I have a table column called separation date with data in this
format:DD/MM/YYYY and sume NULL values.

Now I wat to be this column in datetime format..
I was tryinh toconvert with the following query

SELECT CAST(Separation_date as DATETIME) from TX_Job_History
where I am getting the follwing error:

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

Please help me out to solve this..

Thanks in advance.

Converting varchar to datetime - Uri Dimant

Hi
Add ISDATE(col)=1 in WHERE condition. If it does not help, please post
sample data to be tested

Converting varchar to datetime - Daniel Crichton

Prabha wrote  on Thu, 21 Feb 2008 02:22:01 -0800:







What are your regional settings? Do all the rows have a valid date?

Could be that either the regional settings are causing the date conversion
to read them as MM/DD/YYYY (which will break on any day over 12), or you've
got invalid dates in the column. You might have better luck first using
SUBSTRING to split the dates up and reformat them as YYYYMMDD format which
is non-ambiguous.

--
Dan

Converting varchar to datetime - Tibor Karaszi

Use CONVERT instead of CAST and specify a third format code that matches the string format of your
data. The format codes are documented in Books Online, CONVERT.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Converting varchar to datetime - Plamen Ratchev

Style 103 should do it fine:

SELECT CONVERT(DATETIME, dt, 103)
FROM (SELECT '22/01/2008' UNION
SELECT '25/01/2008' UNION
SELECT NULL) AS D(dt)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Converting varchar to datetime - Madhivanan

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-wi=
th-care.aspx