CODEWITHSUNDEEP
sql-serversql-server-2005
user988122
user988122

Reputation: 19

Convert text date to datetime SQL server

I need to select and insert the info from table1 database1 and insert it into table1 database2. The problem is that these records have a varchar date (12 december 2012) I want to convert that date to datetime, and then insert it into another table of the database2. How can I do that?

I'm doing a test with this code:

DECLARE @date nvarchar(30)
SET @date = '12 of enero of 2012'

PRINT @date
PRINT cast((REPLACE('12 of enero of 2012', 'of', '')as datetime)

Upvotes: 0

Views: 3032

Answers (3)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

You can simply cast it, like so

SELECT CAST(REPLACE('12 of december of 2011', 'of ', '') AS DATETIME)

so all you have to do for insert is,

-- In your last update I noticed you are using Spanish
-- So you'll have to put <b>SET LANGUAGE SPANISH</b> before your query
INSERT INTO TABLE1 (MyDateTimeField)
SELECT CAST(REPLACE(MyVarCharThatContainsDateTimeField, 'of ', '') AS DATETIME)
FROM TABLE2

Notice that this will throw an error if your string is not a date:

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

Upvotes: 1

Tim Lehner
Tim Lehner

Reputation: 15261

Based upon your test code (and other answers):

SET LANGUAGE Spanish

DECLARE @date nvarchar(30)
SET @date = '12 of enero of 2012'

PRINT @date
PRINT cast(REPLACE(@date, 'of', '') as datetime)

Upvotes: 0

Tevo D
Tevo D

Reputation: 3381

The CONVERT function works to convert this date format.

convert(datetime, '12 december 2012')

Upvotes: 0

Related Questions