[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to Convert VarChar to DateTime in Sql Server 2005

Posted on 2011-10-20
Medium Priority
Last Modified: 2012-05-12
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:

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.

Question by:SchmidtZ28
  • 2
LVL 15

Accepted Solution

tim_cs earned 2000 total points
ID: 37001701
Don't know much about MySQL but it looks like they use a base date of 1/1/1969 UTC.  So using this you should be able to cast to a SQL DateTime.

SELECT DATEADD(ss,posted ,'1/1/1968 19:00')
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 37001746
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'

Author Closing Comment

ID: 37001751
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
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 37001785
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question