Examples

Here go simple examples on datetime functions. Please, see more here and in SQL Server Books online on your machine.

1. Exract only the hour-part from the datetime-field movietime. Reject all the output rows except top 5.

select top 5 DATEPART ( hh , movietime ) as only_hour from movies2theaters
            ||
            ||
            ||
            ||
            \/
only_hour
---------
16
19
21
13
15
(5 row(s) affected)

2. Compute how many hours are between now and movietime. Return only top 1% of entire result set.

select top 1 percent DATEDIFF ( hh , movietime, getdate() ) as number_of_hours from movies2theaters
            ||
            ||
            ||
            ||
            \/
number_of_hours
---------
25943
25940
25938
25946
25944
25943
25941
25938
(8 row(s) affected)

3. See what would be the schedule like in 500 weeks after movietime. Return the entire result set for Movie-010. Notice, that DATEADD discards fractional part.

select all dateadd(wk, 500.5, movietime) as in500weeks from movies2theaters where moviecode='Movie-010'
            ||
            ||
            ||
            ||
            \/
in500weeks
---------
2010-05-21 23:50:00.000
2010-05-21 14:30:00.000
2010-05-21 16:50:00.000
2010-05-21 19:00:00.000
2010-05-21 21:10:00.000
2010-05-21 23:50:00.000
2010-05-21 14:30:00.000
2010-05-21 16:50:00.000
2010-05-21 19:00:00.000
2010-05-21 21:10:00.000
2010-05-21 23:50:00.000
2010-05-21 14:30:00.000
2010-05-21 16:50:00.000
2010-05-21 19:00:00.000
2010-05-21 21:10:00.000
(15 row(s) affected)