How to Convert VarChar to DateTime in Sql Server 2005

Posted on 2011-10-20
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
    LVL 15

    Accepted Solution

    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
    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

    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
    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now