CODEWITHSUNDEEP
sql-serverdatedatetimesql-server-2014
Vivek Mishra
Vivek Mishra

Reputation: 1794

How to change date-time format?

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.

Upvotes: 1

Views: 135

Answers (1)

Tanner
Tanner

Reputation: 22733

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: YYYY-MM-DD HH:MM:SS.

Here's a sample that uses a case statement to provide optional formatting of your two date formats, using the presence of the - character:

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
FROM    #temp

DROP TABLE #temp

Produces:

FormattedToDate
2015-05-22 23:09:39.000
2015-05-19 22:55:05.000

Upvotes: 1

Related Questions