?
Solved

Convert Date Format in SQL Query

Posted on 2006-04-06
5
Medium Priority
?
18,668 Views
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
0
Comment
Question by:DavidNPD
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 16392758
SELECT     cfs_no, rec_dt, CONVERT(varchar, rec_dt, 103) AS NewDate
FROM         dba.cfs
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 16392770
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
0
 

Author Comment

by:DavidNPD
ID: 16392798
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 :

0
 

Author Comment

by:DavidNPD
ID: 16392891
TimCottee
I just realized that the month and day is now swapped, it is no longer 04/06/2006 but 06/04/2006.

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

Accepted Solution

by:
SjoerdVerweij earned 200 total points
ID: 16393381
Select  cfs_no, rec_dt, convert(varchar,rec_dt,1) + ' ' + replace(convert(varchar(5),rec_dt,114),':','') As NewDate
FROM         dba.cfs
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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