SELECT DATEPART(HOUR, GETDATE());
The DATEPART() function returns a specified part of a date.
https://www.w3schools.com/sql/func_sqlserver_datepart.asp
https://stackoverflow.com/questions/1114307/extracting-hours-from-a-datetime-sql-server-2005
// Records for everymonth in year
SELECT COUNT(*)
FROM table_emp
WHERE YEAR(ARR_DATE) = '2012'
GROUP BY MONTH(ARR_DATE)
https://stackoverflow.com/questions/9888263/count-records-for-every-month-in-a-year/9888386
// Count date time per hour
SELECT CAST(StartDate as date) AS ForDate,
DATEPART(hour,StartDate) AS OnHour,
COUNT(*) AS Totals
FROM #Events
GROUP BY CAST(StartDate as date),
DATEPART(hour,StartDate)
https://stackoverflow.com/questions/7001718/sql-server-group-by-count-of-datetime-per-hour
The CAST() function converts a value (of any type) into a specified datatype.
SELECT CAST('2017-08-25' AS date); //2017-08-25
SELECT CAST('2017-08-25' AS datetime); //2017-08-25 00:00:00.000
https://www.w3schools.com/sql/func_sqlserver_cast.asp
// Hour average
SELECT CAST(FLOOR(CAST(timestamp AS float)) AS datetime) AS day --strip time
, DATEPART(hh, timestamp) AS hour
, AVG(value) AS average
FROM times
GROUP BY CAST(FLOOR(CAST(timestamp AS float)) AS datetime)
, DATEPART(hh, timestamp)
https://stackoverflow.com/questions/9744144/take-hourly-average-in-sql
// Group by date need to cast column into date
GROUP BY CAST(column_date_a AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' as DATE), DATEPART(HOUR, column_date_a AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time')
// Cast to a differnet timezone, requires cast to datetime
CAST(GETDATE() AS DATETIME) AT TIME ZONE 'Pacific Standard Time'
No comments:
Post a Comment