Sql Convert Nvarchar(255) To Datetime Problem

I'm using SQL server 2008. I have 2 Tables: Table 1 and Table 2. Table 1 has 1 column called: OldDate which is nvarchar(255), null Table 2 has 1 column called: NewDate which is dat

Solution 1:

try using

CONVERT(datetime,OldDate ,103)

the "103" tells the converter that the format is dd/mm/yyyy

EDIT

here is a good like with many examples: http://www.sqlusa.com/bestpractices/datetimeconversion/

You seem to have m/d/y as well as d/m/y data, this is about the best you can do:

DECLARE@Table1table (PK int, OldDate nvarchar(255) null)
DECLARE@Table2table (PK int, NewDate datetime notnull)
INSERT@Table1VALUES (1,'26/07/03')
INSERT@Table1VALUES (2,null)
INSERT@Table1VALUES (3,null)
INSERT@Table1VALUES (4,'23/07/2003')
INSERT@Table1VALUES (5,'7/26/2003')
INSERT@Table1VALUES (6,null)
INSERT@Table1VALUES (7,'28/07/03')

SET DATEFORMAT dmy

INSERTINTO@Table2
        (PK, NewDate)
    SELECT
        PK,
        CASEWHEN ISDATE(OldDate)=1THEN OldDate
            ELSE'1/1/1900'ENDFROM@Table1SET DATEFORMAT mdy

UPDATE t2
    SET NewDate=OldDate
    FROM@Table2           t2
        INNERJOIN@Table1 t1 ON t2.PK=t1.PK
    WHERE t2.NewDate='1/1/1900'AND ISDATE(OldDate)=1SELECT*FROM@Table2

OUTPUT:

PKNewDate----------------------------------12003-07-26 00:00:00.00021900-01-01 00:00:00.00031900-01-01 00:00:00.00042003-07-23 00:00:00.00052003-07-26 00:00:00.00061900-01-01 00:00:00.00072003-07-28 00:00:00.000(7row(s)affected)

I used '1/1/1900' because you have NewDate as NOT NULL.

Solution 2:

It seems you have incorrect data (or a typo).

Some of dates are in British/French standard dd/mm/yyyy(see code 103) and some in USA standard mm/dd/yyyy(code 101).

For the first case you could try CONVERT(datetime, [OldDate], 103),

for the second CONVERT(datetime, [OldDate], 101)