Format DATETIME field in a Select Statement from MS SQL 2005?

Posted on 2009-04-16
Last Modified: 2012-05-06
One of them any columns of information I need from a MSSQL database contain DATETIME, I'm pulling this off of a server that is not local and importing the results into a local MySQL Server. I have two problems. First of all, the MSSQL Server is in UTC time, and I am in Eastern time, I have included what I use to fix this problem, I need to know now how to convert the Fields from MSSQLs format, to MySQL's:
DateTime in MSSQL:
4/3/2009 2:43AM
DateTime in MySQL (what I need the output from MSSQL to look like):
2009-04-03 02:43:00

Help would be much appreciated
This is how I convert from UTC to Eastern time:

declare @utcoffset datetime;

set @utcoffset = cast(getutcdate() - getdate() as decimal(12,7));

Then when I select a datetime field I do:

select field - @utcoffset, fiel2, field3 from table.

thus I'm assuming I would need to do select convert(field) - @utcoffset, or select convert(field - @utcoffset).

Open in new window

Question by:Madison_Hollister
    1 Comment
    LVL 142

    Accepted Solution

    in mssql, to get the format you got in mysql, use this expressoin:
    CONVERT(VARCHAR(20), your_date_time, 120)

    Open in new window


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    731 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

    15 Experts available now in Live!

    Get 1:1 Help Now