SQL Server datetime in 12AM/PM format without the millisecond

I would like to return my datetime data to the following format:

mm/dd/yy hh:mi:ssAM

According to the CONVERT documentation the closest thing that matches my spec is '131'

SELECT CONVERT(varchar, GETDATE(), 131)

but it doesn't exactly match my specifications.

EDIT: I ended up doing the date formatting in my application layer which is PHP.

It was as simple as using the strtotime function to generate a UNIX timestamp and p开发者_如何学Cass it into date funciton.

$date = date('m/d/Y g:ia', strtotime($row['date_time']));

Try this:

SELECT CONVERT(varchar, GETDATE(), 101) + 
REPLACE(CONVERT(varchar, GETDATE(), 0),REPLACE(CONVERT(varchar, GETDATE(),107), ',',''),'')

This basically uses a method that's what you want for the DATE, then adds it to the time portion of what you want for the time only.

Although I feel that formatting should really be part of the UI, I guess if you really need it the below works - I'm sure there is an easier way to do this - but make this a UDF, and just use it in whatever query you want.

declare @date varchar(50)
set @date = (select CONVERT(varchar, GETDATE(), 131))
set @date = (select LEFT(@date,len(@date) - 6) + ' ' + RIGHT(@date,2))
set @date = right(@date,LEN(@date) - 11)

select CONVERT(varchar, GETDATE(), 101) + @date

You can assemble it from DATEPART()'s, and create a user defined function from it for easy re-use.

This is untested off the top of my head.. give it a shot:

CREATE FUNCTION fnSpecDate()
 RETURNS VARCHAR(24)
AS
BEGIN

DECLARE @outData VARCHAR(24)

set @outData = 
       CONVERT(varchar,DATEPART(mm,GETDATE())) + "/"
       + CONVERT(varchar,DATEPART(dd,GETDATE())) + "/"
       + CONVERT(varchar,DATEPART(yy,GETDATE())) + " "
       + CASE WHEN DATEPART(hh,GETDATE()) > 12 
                THEN CONVERT(varchar,DATEPART(hh,GETDATE()) - 12)
              WHEN DATEPART(hh,GETDATE()) = 0
                THEN '12'
              ELSE CONVERT(varchar,DATEPART(hh,GETDATE()))
         END + ":"
       + CONVERT(varchar,DATEPART(mm,GETDATE())) + ":"
       + CONVERT(varchar,DATEPART(ss,GETDATE()))
       + CASE WHEN DATEPART(hh,GETDATE()) > 11 
                THEN "PM"
              ELSE "AM"
         END

RETURN @outData

END

Then in any regular query, you can just include dbo.fnSpecDate() as myDate