[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Converting datetime to military format

Posted on 2007-10-01
10
Medium Priority
?
2,601 Views
Last Modified: 2008-01-09
I have a datetime field that I would like to convert to military format.  I attempted:
CONVERT(CHAR, t_ptdata.entered_for_date, 120) AS entered_for_date,  which makes the time military time, but the date format portion of the field is yyyymmdd and I would like to have it display mm/dd/yyyy.  
0
Comment
Question by:PHS_IT
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 19991561
Try this instead:

select CONVERT(CHAR, t_ptdata.entered_for_date, 101)
0
 
LVL 25

Expert Comment

by:jogos
ID: 19991582
SET DATEFORMAT mdy;

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19991601
select CONVERT(varchar(10) , t_ptdata.entered_for_date, 101)
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 500 total points
ID: 19991604
select
REPLACE(CONVERT(CHAR, t_ptdata.entered_for_date, 101), '/', '-')
+ ' ' +
RIGHT(RTRIM(CONVERT(CHAR, t_ptdata.entered_for_date, 120)), 8)
FROM <TABLENAME>
0
 
LVL 29

Expert Comment

by:Nightman
ID: 19991642
>>SET DATEFORMAT mdy;

FYI:

Please do *not* use this inline in T-SQL code - this will force a recompile of any stored procedure (CPU pressure) and discard of any cached query plan for prepared statements(and stored procedures).

This places a performance overhead on your SQL Server - for large systems this can become prohibitive.
0
 

Author Comment

by:PHS_IT
ID: 19991727
SQL SERVER DBA:
Your code worked.  I figured it was a little more complicated than just using convert.  Any way to drop off the seconds?  

Nightman and aneeshattingal--your code was giving me the date only.  Nightman--thanks for the warning about previously posted code.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 19992166
This will trim the seconds for you:

SELECT CONVERT(varchar(10), getdate(), 101) + ' ' + CONVERT(varchar(6), getdate(), 108)+'00'
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 19992956
SELECT CONVERT(CHAR(11), getdate(), 101) + CONVERT(CHAR(5), getdate(), 8)
0
 

Author Comment

by:PHS_IT
ID: 19993073
Thanks, Scott.  Good to hear from you again!  That worked for me.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19993212
Np; and thx for the other comment!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

830 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