1> /* Advanced SQL Programming Fall 2002*/
2> /* */
3> /* In-class/take-home exercises, Sept. 30th */
4> /* */
5> use evelyn
1> /* */
2> /* How long till Dec. 9th in months? days? months and days? */
3> /* */
4> select datediff(mm, 'Sep 30 2002', 'Dec 9 2002') months
5> select datediff(dd, '9/30/2002', '12/9/2002') days
6> select datediff(mm, '30 Sep 2002', '30 Nov 2002'), "months and ",
7> datediff(dd, '11/30/2002', '12/9/2002'), "days"
months
-----------
3
(1 row affected)
days
-----------
70
(1 row affected)
----------- ----------- ----------- ----
2 months and 9 days
(1 row affected)
1> /* */
2> /* How long since our first class in wks? days? hrs? days+hrs? */
3> /* */
4> select datediff(wk, '9/9/2002 6:30PM', '9/30/2002 8:00PM') weeks
5> select datediff(dd, '9/9/2002 18:30', '9/30/2002 20:00') days
6> select datediff(hh, '9/9/2002 18:30', '9/30/2002 20:00') hours
7> select datediff(dd, '9/9/2002 18:30', '9/30/2002 20:00'), "days and ",
8> datediff(hh, '9/9/2002 18:30', '9/30/2002 20:00') -
9> datediff(dd, '9/9/2002 18:30', '9/30/2002 20:00') * 24, "hour(s)"
weeks
-----------
3
(1 row affected)
days
-----------
21
(1 row affected)
hours
-----------
505
(1 row affected)
----------- --------- ----------- -------
21 days and 1 hour(s)
(1 row affected)
1> /* */
2> /* Day of week of next birthday. */
3> /* */
4> select datename(dw, '5/9/2003') as DayName
5> select datepart(dw, '5/9/2003') as DayNum
DayName
------------------------------
Friday
(1 row affected)
DayNum
-----------
6
(1 row affected)
1> /* */
2> /* Exact age of someone born on July 10th, 1975 (yr, mo, days) */
3> /* Two different methods, each with some formatting: */
4> /* */
5> select datediff(yy, '7/10/1975', getdate()), "years",
6> (datediff(mm, '7/10/1975', getdate()) -
7> datediff(yy, '7/10/1975', getdate()) * 12), "month(s)",
8> (datediff(dd, '7/10/1975', getdate()) -
9> datediff(mm, '7/10/1975', getdate()) *30) -
10> datediff(yy, '7/10/1975', getdate()) * 5.25, "day(s)"
----------- ----- ----------- -------- ------------------ ------
27 years 3 month(s) 3.25 day(s)
(1 row affected)
1> /* */
2> select
3> convert(char(2), datediff(yy, '7/10/1975', getdate())), "years",
4> convert(char(2), (datediff(mm, '7/10/2002', getdate()))), "month(s)",
5> convert(char(2), (datediff(dd, '10/10/2002', getdate()))), "day(s)"
-- ----- -- -------- -- ------
27 years 3 month(s) 1 day(s)
(1 row affected)
1> /* */
2> /* (Question about today's date omitted - see T-SQL Web pages. */
3> /* */
4> /* How many days did it take to ship orders? (Sample database) */
5> /* */
6> select orderdate, shipdate, datediff(dd, orderdate, shipdate) as
7> DaysToShip
8> from ordermaster om, orderdetail od
9> where om.ordnum = od.ordnum
10> and shipdate is not null
orderdate shipdate DaysToShip
-------------------------- -------------------------- -----------
Jan 2 1999 2:30AM Jan 1 1999 12:00AM -1
Jan 2 1999 2:30AM Jan 5 1999 12:00AM 3
Jan 2 1999 12:00AM Jan 2 1999 12:00AM 0
Jan 2 1999 12:00AM Jan 5 1999 12:00AM 3
Jan 2 1999 12:00AM Jan 5 1999 12:00AM 3
Jan 2 1999 12:00AM Jan 5 1999 12:00AM 3
Jan 2 1999 12:00AM Jan 4 1999 12:00AM 2
Jan 2 1999 12:00AM Jan 5 1999 12:00AM 3
Jan 2 1999 12:00AM Jan 5 1999 12:00AM 3
Jan 2 1999 12:00AM Jan 4 1999 12:00AM 2
Jan 2 1999 12:00AM Jan 4 1999 12:00AM 2
Jan 2 1999 12:00AM Jan 2 1999 12:00AM 0
Jan 2 1999 12:00AM Jan 3 1999 12:00AM 1
Jan 2 1999 12:00AM Jan 5 1999 12:00AM 3
Jan 5 1999 2:30PM Jan 5 1999 12:00AM 0
Jan 2 1999 12:00AM Jan 5 1999 12:00AM 3
Jan 3 1999 12:00AM Jan 5 1999 12:00AM 2
(17 rows affected)
1> /* */
2> /* Prevent negative "days to ship" - discuss in class. */
3> /* */
4> /* Date format in legacy database: yyyymmdd, as characters. */
5> /* */
6> select datename(mm, '20010929')
------------------------------
September
(1 row affected)
1> /* */
2> /* Convert to SQL-92 standard date format - two methods. */
3> /* */
4> select convert(char(12), convert(datetime, '20010929'), 101)
------------
09/29/2001
(1 row affected)
1> /* */
2> select substring('20010929', 5, 2) + "/" +
3> substring('20010929', 7, 2) + "/" +
4> substring('20010929', 1,4)
----------
09/29/2001
(1 row affected)
1> /* */
2> dump tran evelyn with no_log