Use Date Functions to Operate with Date Values in Sql Server: SQL Programming

In SQL Programming, programmer can use the date functions of the SQL Server to manipulate date-time values. You can either perform arithmetic operations on date values or parse the date values. Date parsing includes extracting components, such as the day, the month, and the year from a date value.

Programmer can also retrieve the system date and use the value in the date manipulation operations. To retrieve the current system date, you can use the getdate function. The following statement displays the current date:

SELECT getdate ( )

The following SQL query uses the datediff function to calculate the difference between the current date and the date of birth of employees in AdventureWorks, Inc. The date of birth of employees is stored in the BirthDate column of the Employee table.

SELECT datediff (yy, BirthDate, getdate()) AS 'Age'
FROM HumanResources.Employee

Outputs:

The following table lists the date functions provided by SQL Server.

• dateadd, adds the number of date parts to the date
• datediff, calculates the number of date parts between two dates
• datename, returns date part from the listed date, as a character value (for example, October)
• datepart, returns date part from the listed date as an integer
• getdate(), returns the current date and time, day, (date), returns an integer, which represents the day getutcdate, returns the current date of the system in Universal Time Coordinate (UTC) time. UTC time is also known as the Greenwich Mean Time (GMT)
• month, returns an integer, which represents the month
• year, returns an integer which represents the year

SQL Server provides the following abbreviation and values of the datepart function

• Year, Abbreviation -- (yy,yyyy),  may have values (1753-9999)
• Qartr, Abbreviation -- (qq, q), may have values (1-4)
• Month, Abbreviation -- (mm, m), may have values (1-12)
• Day of year, Abbreviation -- (dy, y), may have values (1-366)