sql - nvarchar to datetime
I am trying to cast from nvarchar[255] to datetime as follows:
UPDATE db_table
SET myDateTime = cast([nvarchar255DateTime]
Getting the following SQL scripting error:
Msg 242, Level 16, State 3, Line 3
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
sample data for nvarchar[255]DateTime: 01/10/2010
UPDATE db_table
SET myDateTime = cast([nvarchar255DateTime]
Getting the following SQL scripting error:
Msg 242, Level 16, State 3, Line 3
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
sample data for nvarchar[255]DateTime: 01/10/2010
use this with the appropriate format parameter..
UPDATE db_table
SET myDateTime = CONVERT(datetime, nvarchar255DateTime, 101)
UPDATE db_table
SET myDateTime = CONVERT(datetime, nvarchar255DateTime, 101)
ASKER
Mez4343: thank you for the feedback - at the moment I am getting the following sql scripting error:
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
ASKER
tigin44: getting the following sql scripting error:
Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
so you have values having irregular formats... check the values and correct or discard the irregular formated values...
Run this and check the non-dates in your table.
select myDateTime from db_table where ISDATE(myDateTime) = 0ASKER
Sharath_123: out of 350000 records -- the above script returned 130000
What does this mean please?
What does this mean please?
That means those are not proper dates. Post some of those non-dates.
ASKER
Sharath_123:
29/10/2010
15/11/2010
29/10/2010
15/11/2010
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sharath_123: works perfectly now :-) thank you.
UPDATE db_table
SET myDateTime = CAST(DATEPART(yyyy, nvarchar255DateTime) AS varchar) + '-' +
CAST(DATEPART(mm, nvarchar255DateTime) AS varchar) + '-' +
CAST(DATEPART(dd, nvarchar255DateTime) AS varchar)