Getting Date and Time Parts From DateTime in SQL Server

Introduction

SQL Server provides many functions for obtaining the date and time parts from DateTime. This article explains the following functions to get date and time parts.

1. Datepart Function
2. DateName function
3. Year Function
4. Month Function
5. Day Function

So let's have a look at a practical example of how to get the date and time parts from a DateTime in SQL Server. The example is developed in SQL Server using the SQL Server Management Studio.

The Datepart function in SQL Server

The SQL Server "Datepart" function returns a portion of a SQL Server DateTime field. 

Syntax

The syntax of the "Datepart" built-in date function is as follows:

DATEPART ([Date part], [Datetime])

Here, the <Date part> parameter is the part of the DateTime. DateTime is the name of a SQL Server DateTime field and a portion is one of the following.

Ms	Milliseconds
Yy	Year
Qq	Quarter of the Year
Mm	Month
Dy	The Day of the Year
Dd	Day of the Month
Wk	Week
Dw	The Day of the Week
Hh	Hour
Mi	Minute
Ss	Second

Example

Select  getdate() as CurrentDate Go 
Select 
  datepart(
    Yy, 
    getdate()
  ) As Year Go 
Select 
  datepart(
    Mm, 
    getdate()
  ) As Month Go 
Select 
  datepart(
    Dd, 
    getdate()
  ) As Month Go 
Select 
  datepart(
    Wk, 
    getdate()
  ) As Week Go 
Select 
  datepart(
    Dw, 
    getdate()
  ) As [Day of the Week] Go 
Select 
  datepart(
    Dy, 
    getdate()
  ) As [Day of the Year] Go 
Select 
  datepart(
    Hh, 
    getdate()
  ) As Hour Go 
Select 
  datepart(
    Mi, 
    getdate()
  ) As Minute Go 
Select 
  datepart(
    Ss, 
    getdate()
  ) As Second

Output

DateName function in SQL Server

The DateName function represents the specified datepart of the specified date such as day, month, year, quarter, hour, minute, week, and weekday in SQL Server.

Syntax

DATENAME ( datepart , date )

DateName Function Arguments

• Datepart: Datepart is the part of the date to return.
• Date: Specifies the date.

Creating Table in SQL Server

CREATE TABLE [dbo].[ProductTable] (
         [ID] [nchar](10) NULL, 
         [ProductName] [varchar](max) NULL, 
         [Date] [datetime] NULL
)

Now insert a row into the table, and the current date and time are inserted into the column using the GetDate function.

INSERT INTO [TestDatabase].[dbo].[ProductTable] 
VALUES 
  (
    '1', 
    'Pee Cola', 
    getdate()
  ) go INSERT INTO [TestDatabase].[dbo].[ProductTable] 
VALUES 
  (
    '2', 
    'Jarlsberg', 
    getdate()
  )

The ProductTable table looks like this.

To Get Date part from Date

DAY: Returns the day of the month as an integer.

Example

SELECT 
  [ID], 
  [ProductName], 
  [Date], 
   DATENAME(day,  Date)  as daynumber 
from 
  [ProductTable]

Output

MONTH 

Returns the month as an integer.>

Example

SELECT 
  [ID], 
  [ProductName], 
  [Date], 
   DATENAME(month,  Date)  as [Month] 
from 
  [ProductTable]

Output

YEAR

Returns the 4-digit year as an integer.

Example

SELECT 
  [ID], 
  [ProductName], 
  [Date], 
   DATENAME(YEAR,  Date)  as [Year] 
from 
  [ProductTable]

Output

Hour

Returns the hour value as an integer.

Example

SELECT 
  [ID], 
  [ProductName], 
  [Date], 
   DATENAME(HOUR,  Date)  as [HOUR] 
from 
  [ProductTable]

Output

The Year function in SQL Server

The year function returns an integer that represents the year of the specified date.

Syntax

The syntax of the "Year" built-in date function is as follows:

YEAR ( Date )

Here, the <Date> parameter can be an expression, column expression, user-defined variable, or string literal.

Example

Select  getdate() as CurrentDate Go 
Select 
  Year(
    getdate()
  ) as Year Go 
Select 
  Year('09/12/2012') as Year

Output

The Month function in SQL Server

The "Month" function returns an integer that represents the month of the specified date.

Syntax

The syntax of the Month built-in date function is as follows:

MONTH ( Date )

Here, the <Date> parameter can be an expression, column expression, user-defined variable, or string literal.

Example

Select  getdate() as CurrentDate Go 
Select 
  month(
    getdate()
  ) as Month Go 
Select 
  month('09/12/2012') as Month

Output

The Day function in SQL Server

The "Day" Function returns an integer that represents the Day of the specified date.

Syntax

The syntax of the "Day" built-in date function is as follows:

DAY ( Date )

Here, the <Date> parameter can be an expression, column expression, user-defined variable, or string literal.

Example

Select  getdate() as CurrentDate Go 
Select 
  Day(
    getdate()
  ) as Day Go 
Select 
  Day('09/12/2012') as Day

Output

Conclusion

This article taught us how to get Date and Time Parts From DateTime in SQL Server. For learning more about Date and Time in SQL Server please go through this: Date and Time in SQL Server.