asked on

How to Convert VarChar to DateTime in Sql Server 2005

I've recently imported data from MySQL into my SQL Server 2005 database.

One of the columns is named 'posted' which is saving the time period the record was created in the MySql table, although the Type of field is a Varchar(255), when i imported the data, I imported as Varchar into SQL Server 2005 to ensure no data type converting error occurred.

I now need to convert this to a datetime column type, the following is an example of how the data currently is stored:

posted
1291734120 (the datetime this should reflect is 12/07/10 - 10:02)
1294417394 (the datetime this should reflect is 01/07/11 - 11:23)
1297970783 (the datetime this should reflect is 02/17/11 - 14:26)
1298927798 (the datetime this should reflect is 02/28/11 - 16:16)
1300120638 (the datetime this should reflect is 03/14/11 - 12:37)

Please help me convert the above Varchar in column 'posted' to an appropriate datetime field shown in the parenthesis above that i can use.

thanks

ASKER CERTIFIED SOLUTION

tim_cs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jesus Rodriguez

update YourSQLTable
set NewPostedColumn=dateadd(second,posted,'1/1/1970')

where the NewPostedColumn will be Datetime

will be the convertion
dateadd(second,1291734120,'1/1/1970') will return 'Dec 7 2010 3:02PM'

SchmidtZ28

ASKER

Yes, you are correct. i had to change to the following but you got me in the right direction.

SELECT posted, postedint, DATEADD(ss, postedint, '12/31/1969 19:00') AS Expr1, project
FROM messages

Jesus Rodriguez

declare @time datetime
set @time=dateadd(second,1291734120,'1/1/1970')
print @time
select convert(datetime,cast(@time as nvarchar(max)),120)

this example I runn in sql Server and works as your expectations