Skip to content

Datefunctions

GETDATE() :

The GETDATE() function returns the current database system date and time, in a 'YYYY-MM-DD hh:mm:ss. mmm' format.

1
SELECT getdate()

CURRENTTIMESTAMP :

The CURRENT_TIMESTAMP function returns the current date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format

1
Select Current_TimeStamp

GETUTCDATE :

The GETUTCDATE() function returns the current database system UTC date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format.

1
SELECT getutcdate()

GETUTCDATETIME :

This function in SQL Server is used to return the UTC date and time of the present database system in a 'YYYY-MM-DD hh:mm:ss. mmm' pattern

DAY() :

The DAY() function returns the day of the month (from 1 to 31) for a specified date

1
2
3
4
5
SELECT DAY(getdate())
or
select day('2018-08-10')

ex: select day(hiredate) from emp

MONTH() :

The MONTH() function returns the month part for a specified date (a number from 1 to 12)

1
2
3
4
5
SELECT month(getdate())
or
select month('2018-02-15')

ex: select month(hiredate) from emp

YEAR() :

The YEAR() function returns the year part for a specified date. 

1
2
3
4
5
Select year(getdate())
or
select year('2019-02-05')

ex: select year(hiredate) from emp

DATEPART() :

Return a specified part of a date:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT datepart(interval,date)

interval    Required. The part to return. Can be one of the following values:
year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear, dy, y = Day of the year
day, dd, d = Day of the month
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second
millisecond, ms = Millisecond

1
2
3
4
5
6
7
8
ex: 
select datepart(year,getdate())
select datepart(month,getdate())
select datepart(day,getdate())
select datepart(hour,getdate())
select datepart(MINUTE,getdate())
select datepart(SECOND,getdate())
select datepart(MILLISECOND,getdate())

DATEADD() :

The DATEADD() function adds a time/date interval to a date and then returns the date.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT DATEADD(interval,number,date)

interval    Required. The part to return. Can be one of the following values:
year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear, dy, y = Day of the year
day, dd, d = Day of the month
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second
millisecond, ms = Millisecond

1
2
3
4
5
6
7
8
ex: 
select dateadd(year,1,getdate())
select dateadd(month,2,getdate())
select dateadd(day,10,getdate())
select dateadd(hour,12,getdate())
select dateadd(MINUTE,50,getdate())
select dateadd(SECOND,100,getdate())
select dateadd(MILLISECOND,60,getdate())

DATEDIFF() :

The DATEDIFF() function returns the difference between two dates

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT DATEDIFF(interval,DATE1,DATE2)

interval    Required. The part to return. Can be one of the following values:
year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear, dy, y = Day of the year
day, dd, d = Day of the month
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second

1
2
3
4
5
ex: 

select DATEDIFF(year,'2020-05-01',getdate())
select DATEDIFF(month,'2020-05-01',getdate())
select DATEDIFF(day,'2020-05-01',getdate())

DATENAME()

The DATENAME() function returns a specified part of a date.

This function returns the result as a string value

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
DATENAME(interval, date)


interval    Required. The part to return. Can be one of the following values:
year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear, dy, y = Day of the year
day, dd, d = Day of the month
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second

1
2
3
4
5
6
EX :

select DATENAME(year,getdate())
select DATENAME(month,getdate())
Select DATENAME(dw,getdate())
select DATENAME(day,getdate())