The ANSI Standard version of this is a little different:
::= EXTRACT FROM ::= | ::= TIMEZONEHOUR | TIMEZONEMINUTE ::= |
The is the only place SQL has fields in SQL; I wish noobs would quit confusing fields and columns. The function can take a first argument of DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, SECOND. The DATE and TIME are built from the obvious fields. T-SQL does not have teh INTERVAL temporal types yet.
A useful idiom is a report period calendar. It gives a name to a range of dates.
CREATE TABLE ReportPeriods (reportname CHAR(10) NOT NULL PRIMARY KEY, reportstartdate DATE NOT NULL, reportenddate DATE NOT NULL, CONSTRAINT dateordering CHECK (reportstartdate <= reportend_date), etc);
These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created.
I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.
Since SQL is a database language, we prefer to do look ups and not calculations. I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.
You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate. WHERE sale_day LIKE '2012W26-'
There are several websites with calendars you can cur & paste, but you can start uyoure serch with: http://www.calendar-365.com/week-number.html
Use a table of time slots set to one more decimal second of precision than your data. You can now use temporal math to add it to a DATE to get a full DATETIME2(0)
CREATE TABLE Timeslots (starttime TIME(1) NOT NULL, (endtime TIME(1) NOT NULL, CHECK (starttime < endtime));
INSERT INTO Timeslots --- put data into 15-minute slots VALUES ('00:00:00.0', '00:14:59.9'), ('00:15:00.0', '00:29:59.9'), ('00:30:00.0', '00:44:59.9'), ('00:45:00.0', '01:00:59.9'), .. ('23:45:00.0', '23:59:59.9');
Here is the basic query for rounding down to a time slot.
SELECT CAST (@intimestamp AS DATE), T.starttime FROM Timeslots AS T WHERE CAST (@intimestamp AS TIME) BETWEEN T.starttime AND T.end_time;