I have a
RegDate column of
nvarchar(max) type in my table in which dates are stored in
mm/dd/yyyy (5/22/2015 11:09:39 PM) and
dd-mm-yyyy (19-05-2015 22:55:05) format. I want to get all these entries in one format i.e.
dd/mm/yyyy. I tried to convert it by using
Convert(varchar(10),cast(vr.RegDate as DATETIME),105) as RegistrationDate
but it gives following error:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Please help me regarding this problem.
Best How To :
You need to determine the format of the value you are converting before you can convert it. If it's simply between those two formats, you can simply search for
- to determine it's format.
I would also suggest storing the value in a
datetime column as opposed to a
varchar, and if you can't do that for whatever reason, you should definitely store it in an ISO format:
Here's a sample that uses a case statement to provide optional formatting of your two date formats, using the presence of the
CREATE TABLE #temp ( RegDate VARCHAR(50) )
INSERT INTO #temp
( RegDate )
VALUES ( '5/22/2015 11:09:39 PM' ),
( '19-05-2015 22:55:05' )
SELECT CASE WHEN CHARINDEX('-', RegDate) != 0
THEN CONVERT(DATETIME, RegDate, 105)
ELSE CONVERT(DATETIME, RegDate, 101)
END AS FormattedToDate
DROP TABLE #temp