asked on

SQL date format

I want the SQL datetime column to return the output in mm/dd/yyyy hh:mm:ss am/pm i.e 12 hour format. I used something like this;
SELECT CONVERT(VARCHAR(10),getdate(),101) +' '+ CONVERT(VARCHAR,getdate(),108) but this would return the time in 24 hour format without am /pm I want it return if it am or pm

chapmandew

use 100 instead of 101

ASKER CERTIFIED SOLUTION

ee_rlee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

saibsk

ASKER

but that doesn't have ss right?

Mark Wills

Well, that actually sucks... the long way...

declare @dt datetime

set @dt = getdate()

select convert(varchar(20),@dt,101) + ' ' +convert(varchar(2),right(00+(case when datepart(hh,@dt)>12 then datepart(hh,@dt) - 12 else datepart(hh,@dt) end),2)) +':'+datename(mi,@dt)+':'+datename(ss,@dt)+' '+case when datepart(hh,@dt) > 12 then 'PM' else 'AM' end

There is a quicker way if milliseconds were OK, or no seconds...

Mark Wills

Put it in a function, then just use that...

ie

select dbo.mdyhmsa (getdate())

create function mdyhmsa (@dt datetime)
returns varchar(30)
as begin
return convert(varchar(20),@dt,101) + ' ' +convert(varchar(2),right(00+(case when datepart(hh,@dt)>12 then datepart(hh,@dt) - 12 else datepart(hh,@dt) end),2)) +':'+datename(mi,@dt)+':'+datename(ss,@dt)+' '+case when datepart(hh,@dt) > 12 then 'PM' else 'AM' end
end

Open in new window