SQL 101 : Date Formatting

Casting a value as DateTime validates it -

-- SQL DateTime Validation
SELECT CAST('2006-03-31' AS datetime) -- Succeeds, 31 days in March
SELECT CAST('2006-04-31' AS datetime) -- Fails, only 30 days in April

SELECT CAST('2006-02-29' AS datetime) -- Fails, No 29th day in Feb 2006
SELECT CAST('2004-02-29' AS datetime) -- Succeeds, was a leap year
SELECT CAST('2002-02-29' AS datetime) -- Fails, No 29th day in Feb 2002
SELECT CAST('2000-02-29' AS datetime) -- Succeeds, was a leap year

When language is set to us_english, date format of 'mdy' expects the month to be provided before the day.

SET LANGUAGE us_english  -- Changed language setting to us_english.
SELECT CAST('2006-03-31' AS datetime) -- works
SELECT CAST('03-31-2006' AS datetime) -- works
SELECT CAST('31-03-2006' AS datetime) -- fails

When language is set to british, date format of 'dmy' expects the day to be provided before the month.

SET LANGUAGE british  -- Changed language setting to British.
SELECT CAST('2006-03-31' AS datetime)  -- fails
SELECT CAST('2006-31-03' AS datetime)  -- works
SELECT CAST('31-03-2006' AS datetime) -- works

In both of these cases, SQL correctly interprets the year, whether at the start or end of the string.
The failures generate -
Msg 242, Level 16, State 3, Line 7
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Using set dateformat to override the language setting -

SET DATEFORMAT 'dmy'
-- example
SET LANGUAGE us_english  -- Changed language setting to us_english.
SET DATEFORMAT 'dmy'
SELECT CAST('31-03-2006' AS datetime) -- now succeeds.

Best Practice is to use a language neutral date format -

-- ISO 8601 format doesnt care about language >
SELECT CAST('2006-03-31T00:00:00' AS datetime)
-- Neither does removing the '-'
SELECT CAST('20060331' AS datetime)

More on language neutral date formats here - http://www.karaszi.com/SQLServer/info_datetime.asp

2 ways to get the current time & date -

SELECT CURRENT_TIMESTAMP
SELECT getdate()