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),getdat
SELECT CONVERT(VARCHAR(10),getdat
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
Last Comment
Mark Wills
use 100 instead of 101
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
but that doesn't have ss right?
Well, that actually sucks... the long way...
declare @dt datetime
set @dt = getdate()
select convert(varchar(20),@dt,10
There is a quicker way if milliseconds were OK, or no seconds...
declare @dt datetime
set @dt = getdate()
select convert(varchar(20),@dt,10
There is a quicker way if milliseconds were OK, or no seconds...
Put it in a function, then just use that...
ie
select dbo.mdyhmsa (getdate())
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