CODEWITHSUNDEEP
sql-server-2008
Dinup Kandel
Dinup Kandel

Reputation: 2505

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 to show time only in HH:MM? if so how to cast it as HH:MM format?

Upvotes: 1

Views: 7124

Answers (4)

Rizwan Patel
Rizwan Patel

Reputation: 538

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

Upvotes: 0

Blazes
Blazes

Reputation: 4779

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

Upvotes: 1

heximal
heximal

Reputation: 10517

look at the t-sql function DATENAME:

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

Upvotes: 0

gbn
gbn

Reputation: 432331

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.

Upvotes: 1

Related Questions