State all the date functions, and how are they used?

Below are some of the date functions

Function

Functionality

Uses

Getdate()

Get the current date and time of the current time zone

It doesn’t want any input parameter

Used to fetch current date and time

Select Getdate()

Datepart()

It is used to extract the date numbers from the date passed as the parameter

Format:

DATEPART(inerval,date)

SELECT

DATEPART(MONTH ,GETDATE ()) MONTH_NUMBER, DATEPART(DAY ,GETDATE ()) DATE_NUMBER, DATEPART(YEAR ,GETDATE ()) YEAR_NUMBER

Datename()

It is used to extract the date names from the date passed as the parameter

Format:

DATENAME(inerval,date)

SELECT DATENAME(MONTH,GETDATE())MONTH_NAME, DATENAME(DAY,GETDATE()) DAY_OF_THE_MONTH, DATENAME(DAYOFYEAR,GETDATE()) DAY_OF_THE_YEAR, DATENAME(QUARTER,GETDATE())QUARTER_NUMBER, DATENAME(WEEK,GETDATE()) WEEK_OF_THE_YEAR,

DATENAME(WEEKDAY,GETDATE()) DAY_NAME, DATENAME(YYYY,GETDATE()) YEAR_NUMBER

Datediff()

This function is used to find the difference between the 2 dates. This function will help in getting the difference in different types such as date/ hour/ seconds etc.

Format:

DATEDIFF(interval,date1 ,date2)

Date1: start date

Date2: end date

SELECT

DATEDIFF(MONTH, GETDATE(),'20-JUN-2021') MONTHS_DIFFERENCE ,

DATEDIFF(DAY, GETDATE(),'20-JUN-2021') DAY_DIFFERENCE,

DATEDIFF(YEAR, GETDATE(),'20-JUN-2021') YEAR_DIFFERENCE,

DATEDIFF(WEEK, GETDATE(),'20-JUN-2021') WEEK_DIFFERENCE,

DATEDIFF(QUARTER, GETDATE(),'20-JUN-2021') QUATER_DIFFERENCE

,DATEDIFF(HOUR, GETDATE(),'20-JUN-2020') HOURS_DIFFERENCE,

DATEDIFF(MINUTE, GETDATE(),'20-JUN-2020') MINUTE_DIFFERENCE,

DATEDIFF(SECOND, GETDATE(),'20-JUN-2020') SECONDS_DIFFERENCE

Dateadd()

This function is used to add any value to dates. We can add/ subtract the time based on sign which we have added in the value

DATEADD(interval, increment, date1)

Increment: will have the number which need to be added/subtracted

Select Dateadd(MONTH, 4,GETDATE())ADD_MONTH, Dateadd(MONTH, -4,GETDATE())SUBTRACT_MONTH , Dateadd(MINUTE, 4,GETDATE())ADD_MINUTES

Current_Timestamp

Current date time with nano seconds

Select Current_Timestamp

Sysdatetime()

Current system date time with nanoseconds

Select Sysdatetime()

Isdate()

This function is used to check the input is date or not. Returns 1 if input is a date/ else return 0

Select Isdate('20-JUN-2020'),Isdate('20-20-2020')

Output: 1,0

Day()

Get the day from the date passed as the parameter

Select Day( getdate())

Getutcdate()

Get current UTC date time

Select Getutcdate()

Month()

Get the month from the date passed as the parameter

Select Month( getdate())

Year()

Get the year from the date passed as the parameter

Select YEAR( getdate())