October 29, 2009 at 3:15 am
Hello,
How can I transform the output of a hour format
FROM to this date 10/29/2009 1:01 PM
TO this date 10/29/2009 01:08 PM
Here's my code:
DECLARE @dt DATETIME
SET @dt = getdate()
SELECT RIGHT(CAST(100+DATEPART(MM,@DT) AS CHAR(3)),2) + '/'
+ RIGHT(CAST(100+DATEPART(DD,@DT) AS CHAR(3)),2) + '/'
+ CAST(DATEPART(YYYY,@DT) AS CHAR(4)) + ' '
+ CASE WHEN DATEPART(HH,@DT) < 13
THEN RIGHT(CAST(100+DATEPART(HH,@DT) AS CHAR(3)),2)
ELSE CAST(DATEPART(HH,@DT)-12 AS CHAR(2))
END + ':'
+ RIGHT(CAST(100+DATEPART(MI,@DT) AS CHAR(3)),2) + ' '
+ CASE WHEN DATEPART(HH,@DT) < 13
THEN 'AM'
ELSE 'PM'
END
October 29, 2009 at 3:28 am
Here is another way of doing so:
select convert(char(5),getdate(),9)
select convert(varchar(10),getdate(),101) + ' ' + convert(char(5),getdate(),8) +
CASE WHEN DATEPART(hh,getdate())>= 12 then ' PM' ELSE ' AM' END
Notice that althgou this can be done with T-SQL code, I think that this type of operations should be done in the presentation layer and not in the database layer.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 29, 2009 at 3:46 am
Thanks for your quick response but how can I set that into non military format?
October 29, 2009 at 3:53 am
I don’t really know what military format is. You asked for a specific format in the first message and I wrote a code that shows the date using the format in your example. If you need a different format, pleas show it. Also can you explain why are you doing to formatting in the database instead of doing it in the presentation layer?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply