January 1, 1753 through December 31,9999

January 1, 1900 through June 6, 2079

January 1, 0001 to December 31, 9999

12:00:00.000000AM to 11:59:59.999999PM

January 1, 0001 to December 31, 9999

12:00:00 AM to 11:59:59:990 PM

select cast("01/03/63" as datetime)
go

 --------------------------
        Jan  3 1963 12:00AM

(1 row affected)

convert (datatype [(length) | (precision[, scale])] 
	[null | not null], expression [, style])

select current_bigdatetime())
------------------------------
Nov 25 1995 10:32:00.010101AM

select current_bigtime())
------------------------------
10:32:00.010101AM

select datepart(us, current_bigtime())
------------------------------
01010

1> select datename(month, current_date())
2> go

------------------------------ 
August 


1> select datepart(month, current_date())
2> go

----------- 
8 

1> select current_time()
2> go

------------------------ 
            12:29PM


1> select datename(minute, current_time())
2> go

------------------------------ 
45

(1 row affected)

dateadd(date_part, integer, {date | time | bigtime | datetime, | bigdatetime})

declare @a date
select @a = "apr 12, 9999"
select dateadd(dd, 1, @a)
--------------------------
Apr 13 9999



select dateadd(mi, -5, convert(time, "14:20:00"))
--------------------------
2:15PM



declare @a datetime
select @a = "apr 12, 2013 14:20:00 "
select dateadd(hh, 25, @a)
-------------------------- 
Apr 13 2013 3:20PM 

datediff(datepart, {date, date | time, time | bigtime, bigtime | datetime, datetime | bigdatetime, bigdatetime}])

declare @a date
declare @b date
select @a = "apr 1, 1999"
select @b = "apr 2, 1999"
select datediff(hh, @a, @b)
-----------
     24

datename(datepart {date | time | bigtime | datetime | bigdatetime})

declare @a bigdatetime
select @a = "apr 12, 0001 00:00:00.010101"
select datename(mm, @a)
------------------------------
April

datepart(date_part {date | time | datetime | bigtime | bigdatetime}))

select datepart(year, pubdate) from titles 
    where type = "trad_cook"

 -----------
        1990 
        1985 
        1987 

declare @a time
select @a = "20:43:22"
select datepart(hh, @a)
-----------
    20

select getdate()

Nov 25 1995 10:32AM

select isdate(stor_id), isdate(date) from sales

---- ---- 
0    1 

select stor_id, discount,
        case
            when lowqty is not NULL then lowqty
            else highqty
        end
from discounts

select title,
    nullif(type, "UNDECIDED")
from titles

select title, 
    case
        when type = "UNDECIDED" then NULL
        else type
    end
from titles