Undocumented DATE and TIME related functions

It is quite surprising to know the undocumented date and time related functions that work in both sql server 2000 and 2005

Run these and see(I think they are self-explanatory )

select {fn current_date()}
select {fn current_time()}
select {fn now()}
select {fn extract(hour from getdate())}
select {fn extract(minute from getdate())}
select {fn extract(second from getdate())}
select {fn extract(day from getdate())}
select {fn extract(month from getdate())}
select {fn extract(year from getdate())}
select {fn dayname(GetDate())}
select {fn monthname(GetDate())}
select {fn month(GetDate())}
select {fn year(GetDate())}

Handle ISDATE() with care

Now-a-days it becomes common practice for newbies to use varchar datatype to store dates. My first advice is to use proper DATETIME datatype. Sometimes it is preferred to use varchar when importing dates from other systems(to avoid any errors if the source dates are in different format). In that case it is required to see if values are actually dates. To check if a value is proper date, it is suggested to use ISDATE()

eg

select isdate('2007-10-12'),isdate('12-12-2006'),isdate('March 12, 2007')

Now it should be noted that the following return 1

select isdate(2007),isdate('2007')

becuase  the values in a ISDATE functions are first casted to datetime

              

select cast(2007 as datetime)                                       
------------------------------------------------------
1905-07-01 00:00:00.000

select cast('2007' as datetime)                                              
------------------------------------------------------
2007-01-01 00:00:00.000

Which are valid dates

So if varchar datatype is used to stored formatted dates (ex YYYYMMDD), to check if it is valid date, you need not only to use ISDATE() function, but also use LEN() function

Consider the following set of data and see the difference

declare @dates table (dates varchar(8))

insert into @dates(dates)Select '20071201' union allSelect '20071111' union allSelect '2007' union allSelect 2007 union allSelect '1800'

select dates from @dateswhere ISDATE(dates) =1

--Result
dates  
--------
20071201
20071111
2007
2007
1800

--Result

select dates from @dateswhere ISDATE(dates) =1 and LEN(dates)=8

dates   

--------

20071201

20071111

SELECT DATENAME(year, '2014/04/28');
Result: '2014'

SELECT DATENAME(yyyy, '2014/04/28');
Result: '2014'

SELECT DATENAME(yy, '2014/04/28');
Result: '2014'

SELECT DATENAME(month, '2014/04/28');
Result: 'April'

SELECT DATENAME(day, '2014/04/28');
Result: '28'

SELECT DATENAME(quarter, '2014/04/28');
Result: '2'

SELECT DATENAME(hour, '2014/04/28 09:49');
Result: '9'

SELECT DATENAME(minute, '2014/04/28 09:49');
Result: '49'

SELECT DATENAME(second, '2014/04/28 09:49:12');
Result: '12'

SELECT DATENAME(millisecond, '2014/04/28 09:49:12.726');
Result: '726'