Konwertuj typ danych czasu do formatu AM PM:
Mam jedną tabelę, która ma dwa pola, takie jak "StartTime" i "EndTime". Typ danych obu kolumn to czas.
Więc wartości tabeli wyglądają następująco:
TableA:
StartTime EndTime
------------------ ----------------
17:30:00.0000000 17:57:00.0000000
Ale potrzebuję wyniku jako
StartTime EndTime
------------------ ----------------
05:30 PM 05:57 PM
Kiedy wybieram tabelę. Jak uzyskać czas w formacie AM PM?
11 answers
Użyj następującej składni do konwersji czasu do formatu AM PM.
Zastąp nazwę pola wartością w następującym zapytaniu.
select CONVERT(varchar(15),CAST('17:30:00.0000000' AS TIME),100)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-31 06:45:44
W SQL 2012 można użyć funkcji Format ().
Https://technet.microsoft.com/en-us/library/hh213505%28v=sql.110%29.aspx
Pomiń odlewanie, jeśli typem kolumny jest (datetime).
Przykład:
SELECT FORMAT(StartTime,'hh:mm tt') AS StartTime
FROM TableA
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-10-23 05:05:06
Oto różne sposoby, w jakie możesz to wyciągnąć (w zależności od potrzeb).
Using the Time DataType:
DECLARE @Time Time = '15:04:46.217'
SELECT --'3:04PM'
CONVERT(VarChar(7), @Time, 0),
--' 3:04PM' --Leading Space.
RIGHT(' ' + CONVERT(VarChar(7), @Time, 0), 7),
--' 3:04 PM' --Space before AM/PM.
STUFF(RIGHT(' ' + CONVERT(VarChar(7), @Time, 0), 7), 6, 0, ' '),
--'03:04 PM' --Leading Zero. This answers the question above.
STUFF(RIGHT('0' + CONVERT(VarChar(7), @Time, 0), 7), 6, 0, ' ')
--'03:04 PM' --This only works in SQL Server 2012 and above. :)
,FORMAT(CAST(@Time as DateTime), 'hh:mm tt')--Comment out for SS08 or less.
Using the DateTime DataType:
DECLARE @Date DateTime = '2016-03-17 15:04:46.217'
SELECT --' 3:04PM' --No space before AM/PM.
RIGHT(CONVERT(VarChar(19), @Date, 0), 7),
--' 3:04 PM' --Space before AM/PM.
STUFF(RIGHT(CONVERT(VarChar(19), @Date, 0), 7), 6, 0, ' '),
--'3:04 PM' --No Leading Space.
LTRIM(STUFF(RIGHT(CONVERT(VarChar(19), @Date, 0), 7), 6, 0, ' ')),
--'03:04 PM' --Leading Zero.
STUFF(REPLACE(RIGHT(CONVERT(VarChar(19), @Date, 0), 7), ' ', '0'), 6, 0, ' ')
--'03:04 PM' --This only works in SQL Server 2012 and above. :)
,FORMAT(@Date, 'hh:mm tt')--Comment line out for SS08 or less.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-03-18 07:19:17
SELECT CONVERT(varchar, StartTime, 100) AS ST,
CONVERT(varchar, EndTime, 100) AS ET
FROM some_table
Lub
SELECT RIGHT('0'+ LTRIM(RIGHT(CONVERT(varchar, StartTime, 100),8)),8) AS ST,
RIGHT('0'+ LTRIM(RIGHT(CONVERT(varchar, EndTime, 100),8)),8) AS ET
FROM some_table
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-31 06:51:02
select case
when DATEPART(hour,'17:30:00.0000000') > 12
then cast((DATEPART(hour,'17:30:00.0000000') - 12) as varCHAR(2)) + ':' + cast(DATEPART(minute,'17:30:00.0000000') as varCHAR(2)) + ' PM'
else cast(DATEPART(hour,'17:30:00.0000000') as varCHAR(2)) + ':' + cast(DATEPART(minute,'17:30:00.0000000') as varCHAR(2)) + ' PM'
end
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-31 06:46:29
Wiele funkcji, ale to da ci to ,czego potrzebujesz (testowane na SQL Server 2008)
Edit: poniższe działanie działa nie tylko dla typu time
, ale również dla typu datetime
.
SELECT SUBSTRING(CONVERT(varchar(20),StartTime,22), 10, 11) AS Start, SUBSTRING(CONVERT(varchar(20),EndTime,22), 10, 11) AS End FROM [TableA];
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-31 07:32:41
This returns like 11:30 am
select CONVERT(VARCHAR(5), FromTime, 108) + ' ' + RIGHT(CONVERT(VARCHAR(30), FromTime, 9),2)
from tablename
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-27 07:35:43
Spróbuj tego:
select CONVERT(VARCHAR(5), ' 4:07PM', 108) + ' ' + RIGHT(CONVERT(VARCHAR(30), ' 4:07PM', 9),2) as ConvertedTime
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-04-06 20:55:37
> SELECT CONVERT(VARCHAR(30), GETDATE(), 100) as date_n_time
> SELECT CONVERT(VARCHAR(20),convert(time,GETDATE()),100) as req_time
> select convert(varchar(20),GETDATE(),103)+' '+convert(varchar(20),convert(time,getdate()),100)
> Result (1):- Jun 9 2018 11:36AM
> result(2):- 11:35AM
> Result (3):- 06/10/2018 11:22AM
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-10-06 05:50:39
Spróbuj tego:
select CONVERT(varchar(15),CAST('2014-05-28 16:07:54.647' AS TIME),100) as CreatedTime
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-04-06 21:19:15
select right(convert(varchar(20),getdate(),100),7)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-06-15 19:01:11