Date Handling with SQL Server - SQL Programmers

DATETIME Column

The date range for a DATETIME column can be January 1, 1753 to December 31, 9999. SQL Server uses 8 bytes to store the DATETIME data type. The first 4 bytes make up an integer value that represents the number of days since January 1, 1753. The second 4 bytes are an integer value that represents the number of milliseconds since midnight.

SMALLDATETIME Column

The date range for a SMALLDATETIME column can be January 1, 1900 to June 6, 2079. SQL Server uses 4 bytes of storage for SMALLDATETIME column. The first 2 bytes integer piece contains the number of days since January 1, 1900 and the second 2 byte integer holds the number of minutes since midnight.

CONVERT (VARCHAR, Column, Style)

We can display the date and time data in many different formats using the CONVERT function with different styles.

Example:

 SELECT GETDATE () 

Result:

2009-10-26 13:39:59.543

Example:

SELECT CONVERT(VARCHAR, GETDATE(), 101)  
    
  

Result:

10/26/2009

Different style numbers result in different formats of date time displayed.

DATEPART:

The DATEPART function returns the value of a specific date part from a date. Use this function to retrieve the months, hours, weekdays, etc. from a given date.

Syntax:

DATEPART (datepart, date)  

Example:

   select getdate()   

Result:

2009-10-26 14:04:07.793

Example:

 select DATEPART(dy, getdate()) 

Result:

299

Example:

select DATEPART(m ,getdate()) 

Result:

10

DATEADD:

The DATEADD function returns a new datetime based on adding a specified date-part interval to a date.

Syntax:

    DATEADD (datepart, number, date)

Example:

select getdate()    

Result:

2009-10-26 14:04:07.793  

Example:

select DATEADD(d,1,getdate()) 

Result:

2009-10-27 14:05:29.950

Example:

 select DATEADD(m,3,getdate())

Result:

2010-01-26 14:05:39.327

DATEDIFF:

The DATEDIFF function returns the number of date-parts between a start date and an end date.

Syntax:

DATEDIFF (datepart, startdate, enddate) 

Example:

select DATEDIFF(d,'2009-10-26 11:01:59.420','2009-11-8 11:01:59.420')   

Result:

13

Example:

    select DATEDIFF(m,'2009-10-26 11:01:59.420','2010-5-30 11:01:59.420')  

Result:

7
DATENAME:

The DATENAME function returns the name of a specified date-part for a given date/time.

Syntax:

    DATENAME ( datepart , date ) 

Example:

  select getdate()  
  

Result:

2009-10-26 14:04:07.793

Example:

select DATENAME (m,getdate())

Result:

October

Example:

    select DATENAME (wk,getdate())  

Result:

44

All the above functions accept the following date-parts,