---
title: "Date and Time Function in SQL"  
description: "Date and Time Function in SQL"  
author: "AVADHESH PATEL"  
published: 2012-09-25  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/1015/date-and-time-function-in-sql  
category: "database"  
tags: ["database"]  
reading_time: 5 minutes  

---

# Date and Time Function in SQL

Date and time [functions](https://www.mindstick.com/forum/34086/jquery-callback-functions) allow manipulating columns and [variables](https://www.mindstick.com/articles/715/php-variables) with [DATETIME data](https://www.mindstick.com/forum/266/datetime-data-type) types.\

##### List of Date and Time Function

1. GETDATE and GETUTCDATE Functions

2. DATEPART Function

3. DATENAME Function

4. DAY, MONTH, and YEAR Functions

5. DATEADD Functions

6. DATEDIFF Function

##### GETDATE and GETUTCDATE Functions

GETDATE and GETUTCDATE functions both return the [current date](https://www.mindstick.com/forum/323/find-the-day-name-and-month-name-from-current-date) and time. However, GETUTCDATE returns the current Universal Time Coordinate (UTC) time, whereas GETDATE returns the date and time on the computer where [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server) is running. The GETUTCDATE() function compares the time zone of SQL Server computer with the UTC time zone. Neither of these functions accepts [parameters](https://www.mindstick.com/articles/87/passing-parameters-in-c-sharp), and they are both non-deterministic.

##### Syntax

GETDATE()

GETUTCDATE()

##### Example

SELECT GETDATE() AS GETDATE, GETUTCDATE() AS GETUTCDATE

##### Screen Shot

![Date and Time Function in SQL](https://www.mindstick.com/mindstickarticle/5af1296e-e157-4fc6-80ed-c8868190ef60/images/387b117b-1929-4f19-8ac2-52ea2fc8848b.png)

##### DATEPART Function

The DATEPART function allows retrieving any part of the date and time variable provided. This function is deterministic except when used with days of the week.

The DATEPART function takes two parameters: the part of the date that you want to retrieve and the date itself. The DATEPART function returns an integer representing any of the following parts of the supplied date: year, quarter, month, day of the year, day, week number, weekday number, hour, minute, second, or millisecond.

##### Syntax

DATEPART ( datepart , date )\
Example1

SELECT DATEPART(month, GETDATE()) AS 'Month Number'

##### Screen Shot

![Date and Time Function in SQL](https://www.mindstick.com/mindstickarticle/5af1296e-e157-4fc6-80ed-c8868190ef60/images/688ba62b-1a3c-464c-9e3c-4e8df301784b.png)

##### Example2

SELECT DATEPART(m, 0) AS MONTH, DATEPART(d, 0) AS DATE, DATEPART(yy, 0) AS YEAR

![Date and Time Function in SQL](https://www.mindstick.com/mindstickarticle/5af1296e-e157-4fc6-80ed-c8868190ef60/images/8a61bab3-f04b-4d48-a063-3720f471f0b5.png)

In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.

DATEPART parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.

| ##### Datepart | ##### Abbreviations |
| --- | --- |
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| weekday | dw |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |

DATENAME Function

The DATENAME nondeterministic function returns the name of the portion of the date and time variable. Just like the DATEPART function, the DATENAME function accepts two parameters: the portion of the date that you want to retrieve and the date. The DATENAME function can be used to retrieve any of the following: name of the year, quarter, month, day of the year, day, week, weekday, hour, minute, second, or millisecond of the specified date.

##### Syntax

DATENAME ( datepart , date )

##### Example

SELECT DATENAME(month, getdate()) AS 'Month Name'

##### Screen Shot

![Date and Time Function in SQL](https://www.mindstick.com/mindstickarticle/5af1296e-e157-4fc6-80ed-c8868190ef60/images/ee4cbf58-b619-413b-9367-2f7a0b8eb817.png)

##### DAY, MONTH, and YEAR Functions

DAY, [MONTH and YEAR](https://www.mindstick.com/forum/159363/how-to-write-sql-query-to-get-the-value-of-the-previous-month-and-year) functions are deterministic. Each of these accepts a single date value as a [parameter](https://www.mindstick.com/blog/450/parameter-class-in-c-sharp) and returns respective portions of the date as an integer.

##### Syntax

DAY ( date )

MONTH( date)

YEAR( date )

##### Example

SELECT DAY(getdate()) AS DAY, MONTH(getdate())AS MONTH, YEAR(getdate())AS YEAR

##### Screen Shot

![Date and Time Function in SQL](https://www.mindstick.com/mindstickarticle/5af1296e-e157-4fc6-80ed-c8868190ef60/images/098dc5b4-2a72-4fe3-ba71-37cb282d0a6e.png)

##### DATEADD Functions

DATEADD function is deterministic; it adds a certain period of time to the existing date and time value.

##### Syntax

DATEADD ( datepart , number, date )

##### Example

SELECT GETDATE()AS 'TODAY DATE', DATEADD(day, 21, getdate()) AS 'EXCEED DATE'

##### Screen Shot

![Date and Time Function in SQL](https://www.mindstick.com/mindstickarticle/5af1296e-e157-4fc6-80ed-c8868190ef60/images/b76f3561-c100-4910-865c-e9e6f2373d36.png)

##### DATEDIFF Function

DATEDIFF function is deterministic; it accepts two DATETIME values and a date portion (minute, hour, day, month, etc) as parameters. DATEDIFF() determines the [difference](https://www.mindstick.com/articles/157114/good-news-or-bad-news-and-the-difference-is) between the two date values passed, expressed in the date portion specified. Notice also that start date should come before the end date, if you'd like to see positive numbers in the result set.

##### Syntax

DATEDIFF ( datepart , startdate , enddate )

##### Example

SELECT DATEDIFF(day, '2012-07-01', '2012-08-27') 'AS NO OF DAY'

##### Screen Shot

![Date and Time Function in SQL](https://www.mindstick.com/mindstickarticle/5af1296e-e157-4fc6-80ed-c8868190ef60/images/7adec5cc-7f99-495f-a66a-f531af809314.png)

Some more example of DATETIME function:-

```
----Today
SELECT GETDATE() 'Today'
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year' 
```

---

Original Source: https://www.mindstick.com/articles/1015/date-and-time-function-in-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.