need to Show time in HH:MM format only

i declare a field as time in sql. it is storing the date in the format as HH:MM:SS. now i want to retrieve the time value in the format HH:MM and show it on the design view. is it possible 开发者_如何学Cto show time only in HH:MM? if so how to cast it as HH:MM format?

SELECT 
    CAST(DATEPART(hour, '2007-06-01 12:15:22') AS nvarchar)+':'+
    CAST(DATEPART(minute, '2007-06-01 12:15:22') AS nvarchar);

How it stores time is internal.

You can't change it in the designers etc because this is how SSMS formats it for display based on your regional settings

You can change to hh:mm using CONVERT to (var)char(5) and style 108 when you query it, but I'd suggest you do this formatting in your client code.

look at the t-sql function DATENAME:

SELECT DATENAME(month, getdate()) AS 'Month Name'

for Am and Pm SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

for Date

CONVERT(VARCHAR(9), GetDate(), 6)as log_date