In SQL Server, there's no built-in function that will return the number of days in a month for a given date.  Here's a link to a user-defined function that returns the number of days in a month for the given input date:

User-Defined Function - Get Number of Days in a Month

To determine if an input expression or a varchar column is a valid date, you can use the ISDATE date function.  The ISDATE date function determines whether an input expression is a valid date and returns 1 if the input expression is a valid date; otherwise, it returns 0.  Here's the syntax of the ISDATE date function

ISDATE ( expression )

expression is an expression to be validated as a date and it should be any expression that returns a VARCHAR data type.

To use the ISDATE date function to identify invalid dates in a VARCHAR column, you can do the following:

SELECT * FROM [dbo].[Transactions]
WHERE ISDATE([TransDate]) = 0

To get the difference in hours between two dates, you will be using the DATEDIFF date function.  The DATEDIFF date function returns the number of date and time boundaries crossed between two specified dates and its syntax is as follows:

DATEDIFF ( datepart , startdate , enddate )

datepart is the parameter that specifies on which part of the date to calculate the difference.  Since we are looking for the difference in hours, we will be passing a value of HOUR or HH in this parameter.  startdate is the beginning date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.  enddate is the ending date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.

To illustrate, to get the number of hours between the time an employee times in for his shift until he times out:

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2006/01/12 08:00 AM'
SET @TimeOut = '2006/01/12 05:00 PM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut) AS [ShiftHours]

The output of this script is 9 hours.

ShiftHours
----------------
9

It is worth to note that when getting the difference in hours using the DATEDIFF with the HOUR specified as the datepart in the parameter, it does not consider the minutes in computing for the hours, as can be seen from this example:

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2006/01/12 08:45 AM'
SET @TimeOut = '2006/01/12 05:00 PM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut) AS [ShiftHours]

The output of this is also 9 hours even if the @TimeIn is 8:45 AM.  Only the hour of both datetime variables is considered when computing for the difference.

To further illustrate, the following will return a value of 0 hours because the hours part of both datetime values are the same.

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2006/01/12 08:00 AM'
SET @TimeOut = '2006/01/12 08:45 AM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut) AS [ShiftHours]
GO

On the other hand, the following will return a value of 1 hour even if the actual difference between the 2 datetime values is just 1 minute.

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2006/01/12 08:59 AM'
SET @TimeOut = '2006/01/12 09:00 AM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut) AS [ShiftHours]
GO

To get the difference in hours between two dates, you will be using the DATEDIFF date function.  The DATEDIFF date function returns the number of date and time boundaries crossed between two specified dates and its syntax is as follows:

DATEDIFF ( datepart , startdate , enddate )

datepart is the parameter that specifies on which part of the date to calculate the difference.  Since we are looking for the difference in hours with partial minutes as decimals, we will be passing a value of MINUTE, MI or N in this parameter instead of HOUR or HH, then divide the result by 60.0 to get the difference in hours.  startdate is the beginning date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.  enddate is the ending date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.

To illustrate the difference between the use of HOUR and MINUTE in determining the difference in hours with decimal part, here's a script to get the number of hours between the time an employee times in for his shift until he times out:

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2006/01/12 08:15 AM'
SET @TimeOut = '2006/01/12 05:00 PM'

SELECT DATEDIFF(HOUR,   @TimeIn, @TimeOut)        AS [Hour Parameter],
       DATEDIFF(MINUTE, @TimeIn, @TimeOut) / 60.0 AS [Minute Parameter]

The output of this script is as follows:

Hour Parameter    Minute Parameter
----------------  ----------------
9                 8.750000        

When getting the difference in hours using the DATEDIFF with the HOUR specified as the datepart in the parameter, it does not consider the minutes in computing for the hours, as can be seen from the example.  To get a more accurate result, use the difference in minutes and divide it by 60.0.  It should be noted here that 60.0 (with the decimal place) be used instead of just 60 because dividing 2 integer values will result into an integer value as well with the decimal truncated, as can be seen from the following:

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2006/01/12 08:15 AM'
SET @TimeOut = '2006/01/12 05:00 PM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut)          AS [Hour Parameter],
       DATEDIFF(MINUTE, @TimeIn, @TimeOut) / 60.0 AS [Minute Decimal],
       DATEDIFF(MINUTE, @TimeIn, @TimeOut) / 60   AS [Minute Integer]

The output of this script is as follows:

Hour Parameter    Minute Decimal    Minute Integer
----------------  ----------------  ----------------
9                 8.750000          8

In SQL Server, there's no built-in function that will determine if a given year is a leap year or not.  Here's a link to a user-defined function that determines whether a given date is a leap year or not:

User-Defined Function - Determine Leap Year

To display a date in the YYYY/MM/DD format, you will be using the CONVERT function.  The syntax of the CONVERT function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression is any valid Microsoft® SQL Server™ expression. data_type is the target system-supplied data type.  length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types.  Lastly, style is the style of date format used to convert datetime or smalldatetime to character data.

To display the date in the YYYY/MM/DD format, the value to be passed to the style parameter will be 111, which is the Japan date standard:

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

If today is December 25, 2000, the output of this will be:

YYYY/MM/DD
------------
2000/12/25

It should be noted that the output is now of VARCHAR data type and not DATETIME data type.

To add or subtract months to a datetime or smalldatetime value, you will use the DATEADD date function.  The DATEADD date function returns a new datetime value based on adding an interval to the specified date.  The syntax of the DATEADD date function is as follows:

DATEADD ( datepart , number, date )

datepart is the parameter that specifies on which part of the date to return a new value.  For months, you can use either MONTH, MM, or M.  number is the value used to increment datepart.  date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Here's an example on how to use the DATEADD date function to increase or decrease a datetime value by a certain number of months:

SELECT DATEADD(MONTH, -12, GETDATE())AS [TwelveMonthsAgo]
SELECT DATEADD(MM, 6, GETDATE()) AS [SixMonthsFromToday]

In SQL Server, there's no built-in function that will return the first day of the week for any given date.  Here's a link to a user-defined function that returns the date of the first day of the week for any given input date:

User-Defined Function - Get First Day of the Week

To get the name of the month instead of the month number, you will use the DATENAME date function.  The DATENAME date function returns a character string representing the specified datepart of the specified date and its syntax is as follows:

DATENAME ( datepart, date )

datepart is the parameter that specified the part of the date to return.  For months, you can use either MONTH, MM or M.  date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Here's an example on how to use the DATENAME date function to get the name of the month of a given date:

DECLARE @ChristmasDay    DATETIME
SET @ChristmasDay = '2000/12/25'

SELECT DATENAME(MONTH, @ChristmasDay) AS [Month Name]

Month Name
---------------
December

To get the name of the day of the week such as Sunday or Monday, you will use the DATENAME date function.  The DATENAME date function returns a character string representing the specified datepart of the specified date and its syntax is as follows:

DATENAME ( datepart, date )

datepart is the parameter that specified the part of the date to return.  For the day of the week, you can use either WEEKDAY or DW.  date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Here's an example on how to use the DATENAME date function to get the name of the day of the week of a given date:

DECLARE @ChristmasDay DATETIME    SET
@ChristmasDay = '2000/12/25' SELECT

DATENAME(WEEKDAY, @ChristmasDay) AS [Day Of Week]

Day Of Week
---------------
Monday