从 DateTime 中提取小时数 (SQL Server 2005)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1114307/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extracting hours from a DateTime (SQL Server 2005)
提问by Dave Markle
I can extract the month and day by using Day(Date()), Month(Date()). I can't extract hours, with HOUR(Date()). I get the following error.
我可以使用Day(Date()),提取月份和日期Month(Date())。我无法提取小时数，使用HOUR(Date()). 我收到以下错误。
'HOUR' is not a recognized built-in function name.
How can I extract hours?
如何提取小时数？
回答by Dave Markle
回答by Milan
... you can use it on any granularity type i.e.:
...您可以在任何粒度类型上使用它，即：
DATEPART(YEAR, [date])
DATEPART(MONTH, [date])
DATEPART(DAY, [date])
DATEPART(HOUR, [date])
DATEPART(MINUTE, [date])
(note: I like the [ ] around the date reserved word though. Of course that's in case your column with timestamp is labeled "date")
（注意：我喜欢日期保留字周围的 [ ]。当然，如果您的带有时间戳的列被标记为“日期”）
回答by Adrian Godong
try this one too:
也试试这个：
DATEPART(HOUR,GETDATE())
回答by relgrem
DATEPART(HOUR, [date])returns the hour in military time ( 00 to 23 )
If you want 1AM, 3PM etc, you need to case it out:
DATEPART(HOUR, [date])返回军用时间的小时数（00 到 23）如果你想要 1AM、3PM 等，你需要区分：
SELECT Run_Time_Hour =
CASE DATEPART(HOUR, R.date_schedule)
WHEN 0 THEN '12AM'
WHEN 1 THEN '1AM'
WHEN 2 THEN '2AM'
WHEN 3 THEN '3AM'
WHEN 4 THEN '4AM'
WHEN 5 THEN '5AM'
WHEN 6 THEN '6AM'
WHEN 7 THEN '7AM'
WHEN 8 THEN '8AM'
WHEN 9 THEN '9AM'
WHEN 10 THEN '10AM'
WHEN 11 THEN '11AM'
WHEN 12 THEN '12PM'
ELSE CONVERT(varchar, DATEPART(HOUR, R.date_schedule)-12) + 'PM'
END
FROM
dbo.ARCHIVE_RUN_SCHEDULE R
回答by armando rodriguez
Try this one too:
也试试这个：
SELECT CONVERT(CHAR(8),GETDATE(),108)
回答by Dani
select case when [am or _pm] ='PM' and datepart(HOUR,time_received)<>12
then dateadd(hour,12,time_received)
else time_received
END
from table
works
作品
回答by Hamid Nadi
The DATEPART() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.
DATEPART() 函数用于返回日期/时间的单个部分，例如年、月、日、小时、分钟等。
datepart ***Abbreviation
year ***yy, yyyy
quarter ***qq, q
month ***mm, m
dayofyear ***dy, y
day ***dd, d
week ***wk, ww
weekday ***dw, w
hour ***hh
minute ***mi, n
second ***ss, s
millisecond ***ms
microsecond ***mcs
nanosecond ***ns
Example
例子
select *
from table001
where datepart(hh,datetime) like 23
回答by Lukasz Szozda
I can't extract hours, with HOUR(Date())
我无法使用 HOUR(Date()) 提取小时数
There is a way to call HOUR(I would not recommend to use it though because there is DATEPARTfunction) using ODBC Scalar Functions:
有一种方法可以使用ODBC 标量函数调用HOUR（我不建议使用它，因为有DATEPART函数）：
SELECT {fn HOUR(GETDATE())} AS hour
回答by Fred
you must use datepart()
你必须使用 datepart()
like
datepart(hour , getdate())
