CODEWITHSUNDEEP
sqlsql-server-2008
Ashu Rawat
Ashu Rawat

Reputation: 21

How to find Only hour value in 12hrs format using sql

I want hour value only from the currenttime

Like if it is 4:31:13 so i want only 4 and that also should be in 12hrs format..

Please help me...

Upvotes: 0

Views: 218

Answers (6)

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

SELECT 
case when DATEPART(hh, time) > 12
then DATEPART(hh, time) - 12 
else DATEPART(hh, time) end as hour
from time;

SAMPLE FIDDLE.

Upvotes: 2

Andriy M
Andriy M

Reputation: 77677

SELECT (DATEPART(HOUR, GETDATE()) + 11) % 12 + 1

At 1 am this will return:

1 + 11 = 12; 12 % 12 = 0; 0 + 1 = 1

At 1 pm (13:00 in 24 hrs format) you'll get:

13 + 11 = 24; 24 % 12 = 0; 0 + 1 = 1

Similarly, at 2 am and at 2 pm (14:00):

 2 + 11 = 13; 13 % 12 = 1; 1 + 1 = 2
14 + 11 = 25; 25 % 12 = 1; 1 + 1 = 2

and so on till 11 am and 11 pm. At 12 am (0:00) & 12 pm (12:00) it will go like this:

 0 + 11 = 11; 11 % 12 = 11; 11 + 1 = 12
12 + 11 = 23; 23 % 12 = 11; 11 + 1 = 12

Upvotes: 1

Alexey A.
Alexey A.

Reputation: 902

This will also correctly handle 12 AM and 12 PM time:

select case datepart(hh, getdate()) 
    when  0 then 12 
    when 12 then 12 
    else datepart(hh, getdate()) % 12 
    end;

Upvotes: 1

Shail
Shail

Reputation: 1575

SELECT DATEPART(hour, '2008-09-27 09:30:00.000')

Refer this

Upvotes: 0

Lajja Thaker
Lajja Thaker

Reputation: 2041

SELECT SUBSTRING(LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)),1,1)

Upvotes: 0

Apprentice
Apprentice

Reputation: 701

I don't know what database engine are you using, but in PostgreSQL you can do this:

select to_char(current_timestamp, 'HH12');

Upvotes: 0

Related Questions