CODEWITHSUNDEEP
sqlsql-serverdate
Mike V.
Mike V.

Reputation: 83

SQL converting Date datatype from Varchar to Date

so I'm trying to convert my date column datatype from varchar to date.

Currently my date is in d/m/yyyy format and I want to convert to standard mm/dd/yyyy

Here's the script that I'm running

update [table]
set [PERIOD]= CONVERT(varchar(20),cast([PERIOD] as date),101)

But I'm getting an error

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

Any tips or am I stuck?

Upvotes: 0

Views: 230

Answers (4)

Sarath Subramanian
Sarath Subramanian

Reputation: 21401

You may have junk value and can get the junk values by executing the following query

SELECT [PERIOD] 
FROM YOURTABLE
WHERE ISDATE([PERIOD]) = 0

To avoid junk values and update the rest try the below

update YOURTABLE
set [PERIOD]= CONVERT(varchar(20),cast([PERIOD] as date),101)
WHERE ISDATE([PERIOD]) = 1

Upvotes: 0

Mohammed
Mohammed

Reputation: 313

I think if you can segregate the day and month and the year by using substring or any other string functions then use the below query 

update tableA
set period = convert(datetime,cast(day(1)as varchar)+'/'+cast(MONTH(1)as varchar)+'/2014',102)

Hope its works.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

Try this.

update [table]
set [PERIOD]= CONVERT(varchar(20),CONVERT(date,[PERIOD] ,103),101)

Problem in your query is 

cast([PERIOD] as date)

You cannot directly convert d/m/yyyy format to date. So use 103 style to convert

Upvotes: -1

Hart CO
Hart CO

Reputation: 34784

I suggest instead of updating one string date to another format of string date, that you add a new DATE or DATETIME field to store the dates properly.

ALTER [table] ADD Proper_Date DATE
GO
UPDATE [table]
SET Proper_Date = convert(date, [PERIOD], 103)

If you must, you can wrap the above in another CONVERT():

update [table]
set [PERIOD]= CONVERT(varchar(20),convert(date,[PERIOD], 103),101)

Upvotes: 3

Related Questions