Varchar(50) into datetime,

1

how to alter a column of doj varchar(50) into datetime, for eg my doj is 27/05/15, it must come
2015/05/27 00:00:00.000

2 
SET DATEFORMAT dmy
SELECT CONVERT(datetime, [doj]) AS [NewDOJ]
FROM YourTable

This will give error if any of your dates are invalid

3

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

4

i have tried

ALTER TABLE dbo.MBD_EmployeeMaster
alter column DOJ datetime;

error
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

5

SO you've got some invalid dates in your data. Often the case when dates are stored as Text rather than a DATE or DATETIME datatype (which would not have allowed the invalid data into the column in the first place).

You need to find the Goofy values, and fix them. You MIGHT find them with

SET DATEFORMAT dmy
SELECT doj, YourPKeyColumn(s)
FROM YourTable
WHERE IsDate(doj) = 0

I'm not confident that that will work in all instances, but its a good start.

You could also try

WHERE     [doj] NOT LIKE '[02][0-9]/0[0-9]/[0-9][0-9]'
      AND [doj] NOT LIKE '3[0-1]/0[0-9]/[0-9][0-9]'
      AND [doj] NOT LIKE '[02][0-9]/1[0-2]/[0-9][0-9]'
      AND [doj] NOT LIKE '3[0-1]/1[0-2]/[0-9][0-9]'

EDIT: Fixed error in above code
but that won't find things like 30-February

What version of SQL are you using? Recent versions allow TRY / CATCH on conversions, which might be your best bet (but I don't think that will help you FIND the broken values, just prevent the program from bombing)

6

Try this sample query

SELECT CONVERT(varchar(8), GETDATE(), 112) 
UNION ALL 
SELECT DATALENGTH(CONVERT(varchar(8), GETDATE(), 112)) 
UNION ALL 
SELECT CONVERT(varchar, GETDATE(), 112)
UNION ALL
SELECT DATALENGTH(CONVERT(varchar, GETDATE(), 112))
7

I've fixed an error in my earlier code, sorry about that. You may have spotted it, but if not I had "IsDate" instead of the column name "[doj]"