SQL SERVER DATEADD()/DATEDIFF() – DATEPART() – AVG() – COUNT() – FIRST() Functions

SQL SERVER DATEADD() FUNCTION

DEFINITION AND USAGE:

The DATEADD() function adds or subtracts a specified time interval from a date.

SYNTAX:

DATEADD(datepart,number,date)

EXAMPLE:

SELECT Order ld,DATEADD(day,20,OrderDate) AS OrderPayDate FROM

Orders

RESULT:

Order id OrderPayDate

   1.             2011-12-27 11:4136.752

SQL SERVER DATEDIFF() FUNCTION

DEFINITION AND USAGE:

The DATEDIFF() function returns the time between two dates.

SYNTAX :

DATEDIFF(datepart,start date,end date)

EXAMPLE:

SELECT DATEDIFF (day ;2011-08-07 “,2011-08-21′) AS DiffDate

RESULT:

OrderId
14

SQL SERVER DATEPART()FUNCTION

DEFINITION AND USAGE:

The DATEPART() function, is used to return a single part of a date/time, such as

year, month, day, hour, minute, etc.

SYNTAX:

datepart(datepart,date)

EXAMPLE:

SELECT DATEPART(yyyy.OrderDate) ASOrderYear,DATEPART(mm,OrderDate)

AS OrderMonth, DATEPART(dd,OrderDate) AS OrderDay, FROM Order WHERE

Orderld=1.

RESULT:

OrderYear OrderMonth OrderDay
2011 12 07

MYSQL GETDATE() FUNCTION

DEFINITION AND USAGE:

The GETDATE() function returns the current date and time from the SQL Server.

SYNTAX:

GETDATE()

EXAMPLE:

SELECT GETDATE() AS CurrentDateTime

RESLUT:

CurrentDateTime

2011-12-07 11:41:36.752

SQL AVG() FUNCTION

THE AVG()FUNCTION The AVG() function returns the average value of a

numeric column.

SYNTAX:

SELECT AVG(column_name) FROM table_name

EXAMPLE:

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

RESULT:

OrderAverage

1100

SQL COUNT() FUNCTION

DEFINITION AND USES:

The COUNT() Function SQL COUNT(column_ name) Syntax The

COUNT(column_name) function returns the number of values (NULL values will

not be counted) of the specified column

SYNTAX:

SELECT COUNT(*) FROM table_name

EXAMPLE:

SELECT COUNT(*) AS NumberOfOrders FROM Orders

RESULT:

NumberOfOrders

6

SQL FIRST() FUNCTION:

SQL FIRST() Syntax SELECT FIRST(column_ name) FROM table _name

SYNTAX:

SELECT FIRST(Column_name) FROM table_ name

EXAMPLE:

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

RESULT:

FirstOrderPrice

1200

SQL FORMAT() FUNCTION

DEFINITION AND USAGES:

The FORMAT() function is used to format how a field is to be displayed.

SYNTAX:

SELECT FORMAT(column_name,format)FROM table_ name

EXAMPLE:

SELECT ProductName, UnitPrice, FORMAT(Now(),’YYYY-MM-DD’) as PerDate

FROM Products

RESULT:

ProductName unitprice predate

Potatoes 2.38 2011/9/21

Pears. 4.51. 2011/9/21

cherries. 5.98. 2011/9/21