Convert Date Format in SQL Query

Posted on 2006-04-06
Last Modified: 2012-06-27
I would like to convert the format of a DateTime field in sql. Currently the field (rec_dt) holds a value such as this "4/6/2006 1:08:48 PM" I would like this to be shown in the query as 04/06/06 1308"

I was trying to use a command such as the one below, but I dont think I have the format correct.

SELECT     cfs_no, rec_dt, CONVERT(datetime, rec_dt, 103) AS NewDate
FROM         dba.cfs
Question by:DavidNPD
    LVL 18

    Expert Comment

    SELECT     cfs_no, rec_dt, CONVERT(varchar, rec_dt, 103) AS NewDate
    FROM         dba.cfs
    LVL 44

    Expert Comment

    Hi DavidNPD,

    Select  cfs_no, rec_dt, convert(varchar,rec_dt,103) + ' ' + convert(varchar(5),rec_dt,114) As NewDate
    FROM         dba.cfs

    Tim Cottee

    Author Comment

    That is really close, but I have a field size issue that I really need to trim down. I am seeing "06/04/2006 11:21" right now, is there a way to make the 2006 only 06 and also to trim out the :


    Author Comment

    I just realized that the month and day is now swapped, it is no longer 04/06/2006 but 06/04/2006.

    that just gives me "06/04/2006", no time and also the swapped month/day
    LVL 18

    Accepted Solution

    Select  cfs_no, rec_dt, convert(varchar,rec_dt,1) + ' ' + replace(convert(varchar(5),rec_dt,114),':','') As NewDate
    FROM         dba.cfs

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 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

    19 Experts available now in Live!

    Get 1:1 Help Now