Link to home
Create AccountLog in
Avatar of PHS_IT
PHS_IT

asked on

Converting datetime to military format

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.  
Avatar of Nightman
Nightman
Flag of Australia image

Try this instead:

select CONVERT(CHAR, t_ptdata.entered_for_date, 101)
SET DATEFORMAT mdy;

Avatar of Aneesh
select CONVERT(varchar(10) , t_ptdata.entered_for_date, 101)
ASKER CERTIFIED SOLUTION
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>>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.
Avatar of PHS_IT
PHS_IT

ASKER

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.
This will trim the seconds for you:

SELECT CONVERT(varchar(10), getdate(), 101) + ' ' + CONVERT(varchar(6), getdate(), 108)+'00'
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of PHS_IT

ASKER

Thanks, Scott.  Good to hear from you again!  That worked for me.
Np; and thx for the other comment!