Getting Date and Time Parts From DateTime in SQL Server

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

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 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

 

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, the current date and time is 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

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

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

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