Mathmatical Function in Sql Sever

Mathmatical Function in Sql Sever:-

• Abs
• Ceiling
• Floor
• Power
• Rand
• Square
• Sqrt
• Round

1.Abs (numeric_expressions)- Abs stands for absolute and returns, the absolute (positive) number.

Syntax- select  ABS(numeric_expression)

Ex-

---Abs functions

select ABS(011) ---return 011

select ABS(-101)--return 101 not include minus sign

select ABS(111.1)---return 111.1

2. Ceiling() & Floor() functions:-Ceiling and Floor functions accept a numeric expression as a single parameter . Ceiling () returns a smallest integer value greater than or equal to the parameter, whereas Floor() returns the largest integer less than or eual to the parameter.

Syntax-    select CEILING(numeric_expression)

    select floor(numeric_expression)

Ex- 

·         select CEILING(12.1) ---returns output 13

·         select CEILING(-12.1) ---returns output -12

·         select floor(12.1) ---returns output 12

·         select floor(-12.1) ---returns output 13

  
3. Power():-  Returns the power value of the specified expression to the specified power.

Syntax:- select POWER(numeric_expression)

Ex-  select POWER(3,2) ---returns output 9

4. Square():- returns the  square of the given number.

Syntax:-  select SQUARE(numeric_expression)

Ex-  select SQUARE(4)---returns output 16

5. Sqrt():-  Returns the square root of the given number.

Syntax:-  select SQRT(numeric_expression)

Ex-  select SQRT(2)---returns sqareroot output 1.4142135623731

6.Random():- Return the random float number between 0 and 1. Rand() function takes an optional seed parameter. when seed value is supplied the RADN() function always returns the same value for the same seed.

Syntax- select RAND([seed_value])

Ex-  select RAND()---returns always 0 to 1 values but almost 0

select rand(1100)---returns output 0.734069628625685

---if we want 1 to 100 number we use random number:

Ex- select (RAND() * 100) --return 0 to 100 (72.1944369495552)

---if we want only integer no float value----

Ex- select floor(RAND() * 100.9)  --return 0 to 100 (72.1944369495552)

7.ROUND() function(numeric_expression, length[function]):- Rounds the given numeric expression based on the given length. This function takes 3 parameters.

1.  Numeric_Expression-: it’s a number that we want to round.
2.  Length:- Length parameters, specify the number of the digits that we want to round do. If the length is a positive number, then the rounding is applied to the number before the decimal.
3. The Optional Function Parameter it used to indicate rounding or truncation operations. 0 indicates rounding , non zero indicates truncation. Default, if it specified is 0.

Ex-:

--if Round 2 places after (to the right)the decimal point

select round(850.556,2)

---output 850.560 (2 decimal values after ignore points indicate 0)

select ROUND(850.556,1)

--output 850.600 (1 decimal values after ignore points indicate 0)

select ROUND(850.556,3)

--output 850.556(3 decimal values after ignore points indicate 0)

--Truncate output 850.556(3 decimal values after ignore points indicate 0)

select ROUND(850.556,1,1)--returns 1 take in left at point side output(850.500)

select ROUND(850.556,2,1)--returns 2 take in left at point side output(850.550)

select ROUND(850.556,3,1)--returns 3 take in left at point side output(850.556)

select ROUND(850.556,-2)---output 850.23260

select ROUND(9069.556,-3)---output 9000.000

select ROUND(850.556,-2)---output 900.000

Read More

DateTime GetDate Datepart DateDiff DateTime2 DATEPART DATEADD Function in SqlServer

DATETIME  functions in sql server:-

1. Datetime data types
2. Datetime functions
3. Understand UTC time and time zone offset.

Datetime :-

UTC Stands for coordinated universal time, based on which , the world regulates clock and time. There are slight differences between GMT and UTC, But for most comman purposes, UTC is synonymous with GMT

DATETIME:-

Data type	Format	Range	Accuracy	Storage size(Bytes)
Time	hh:mm:ss [.nnnnnnn]	00:00:00:0000000 through 23:59:59.9999999	100 nanoseconds	3 to 5
Date	YYYY-MM-DD	0001-01-01 through 9999-12-31	1  day	3
SmallDateTime	YYYY-MM-DD	1900-01-01 through 2079-06-06	1 minute	4
Datetime	YYYY-MM-DD hh:mm:ss [.nnn]	1753-01-01 through 9999-12-31	0.00333 second	8
DateTime2	YYYY-MM-DD hh:mm:ss [.nnnnnnn]	0001-01-01 00:00:00.0000000 Through 9999-12-31 23:59:59.9999999	100 Nan0seconds	6 to 8
DateTimeOffset	YYYY-MM-DD hh:mm:ss [.nnnnnnn] [+|-]hh:mm	01-01-01     00:00:00 0000000 through 9999-12-31 23:59:59.9999999(in UTC)	100 nanoseconds	8 to 10

Function	Date Time Format	Description
GETTIME()	2012-08-31    20:15:04.543	Commanly used
CURRENT_TIMESTAMP	2012-08-31    20:15:04.543	ANSI SQL equilvalent to GETDATE
SYSDATETIME()	2012-08-31    20:15:04.5380028	More fractional seconds precision
SYSDATETIMEOFFSET()	2012-08-31    20:15:04.5380028+01:00	More fractional seconds precision+Time zone offset
GETUTCDATE()	2012-08-31    20:15:04.543	UTC Date and Time
SYSUTCDATETIME()	2012-08-31    20:15:04.5380028	UTC Date and Time , with more fractional seconds precision

Datetime use in some sql functions:

Datepart	abbreviation
Year	yy, yyyy	It returns the total days in year form the current date
quarter	qq,q	it returns the what time in your system(quarter time)
month	mm, m	it returns the current month in system machine
Dayofyear	dy,y	it returns the total day from the current year
Day	dd,d	it returns the current date from the system
week	wk, ww	it returns the current week from the system
weekday	dw	it returns the current day in week from the system(like sun=1,mon=2,tue=3)
Hour	hh	it returns the current hour from the system
minute	mi, n	It returns the current minute from the system
millisecond	Ms	it returns the current millisecond from the system
microsecond	mcs	it returns the current microsecond from the system
nanosecond	Ns	it returns the current nenosecond from the system
TZoffset	Tz

Using query:-

Examples:-

select DATEPART(Y,GETDATE())

---its returns the total count days form the current date

--ouput 33 days in year now

select DATEPART(YY,GETDATE())

---it return the current year from your system

--output 2016 current year

select DATEPART(YYYY,GETDATE())

---it return the current year from your system same as

-- output 2016 current year

select DATEPART(Q,GETDATE())

select DATEPART(QQ,GETDATE())

select DATEPART(QUARTER,GETDATE())

---it returns the what time in your system(quarter time)

-- output 1 (count every 6 hours)

select DATEPART(MONTH,GETDATE())

select DATEPART(M,GETDATE())

select DATEPART(MM,GETDATE())

---it returns the current month in system machine

--output 2 current date

select DATEPART(D,GETDATE())

select DATEPART(DD,GETDATE())

---it returns the current date from the system

---output 2 current date

select DATEPART(DY,GETDATE())

select DATEPART(DY,GETDATE())

---it returns the total day from the current year

---output 139 days in year form current date

select DATEPART(DW,GETDATE())

select DATEPART(W,GETDATE())

---it returns the current date from the system

--output 3 week day count(1-7)

select DATEPART(WEEK,GETDATE())

select DATEPART(WK,GETDATE())

select DATEPART(WW,GETDATE())

---it returns the current date from the system

---output 6 it return week number from year like (1-52)

select DATEPART(HH,GETDATE())

---it returns the current hour from the system

--output 9 clock(1-24)in current system time

select DATEPART(MI,GETDATE())

---it returns the current minute from the system

---output 3 minute(0-59)in current system time

select DATEPART(ss,GETDATE())---

select DATEPART(S,GETDATE())

---it returns the current second from the system

---output 3 second(0-59)in current system time

select DATEPART(MS,GETDATE())

---it returns the current millisecond from the system

---output 670 millisecond in current system time

select DATEPART(MCS,GETDATE())

---it returns the current microsecond from the system

---output 647000 in microsecond in your current system time

select DATEPART(NS,GETDATE())

---it returns the current nenosecond from the system

---output 347000000 nanosecond in your current system time

ISDATE () FUNCTION-  it Checks if the given value is a valid date, time, or date time. Returns 1 for success, 0 for failure.

For Example -:

select ISDATE('kush')

-- output ->  0

select ISDATE(GETDATE())

-- output ->  1

select ISDATE ('2016-01-30 21:24:04.163')

-- output ->  1

select ISDATE ('2016-01-30 09:27:04.1631238')

--Output Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '2016-01-30 09:27:04.1631238'.

DAY, MONTH, AND YEAR:-

DAY ():- Returns the ‘Day number of the month’ of the given date.

Ex-   select day (getdate())---R

select DAY('01/30/2016')---Returns day date

MONTH ():- Returns the ‘month number of the year’ of the given date.

Ex- select MONTH(getdate())---Returns the current month number of month

select MONTH('01/30/2016')---Returns months

YEAR ():- Returns the ‘year number of the given date.

Ex - select YEAR(getdate())---Returns the current number of YEAR

select YEAR('01/30/2016')---Returns YAER

DATENAME:-Returns a string, that represents a part of given date. This function take 2 parameters. The first parameter specify datepart specifies, the part of the date, we want. The second parameter, is the actual date, from which we want the part of the date.

Ex-  select DATENAME(DAY,'2016-01-31 11:54:23.3232323')

---returns integer

select DATENAME(WEEKDAY,'2016-01-31 11:54:23.3232323')

---returns day in string

select DATENAME(MONTH,'2016-01-31 11:54:23.3232323')

---returns month in string

if we want to proper way use in all datetime functions:-

Ex-  select name,DOB,DATENAME(weekday,DOB) as [day],

MONTH(DOB) as monthnumber,

DATENAME (month,(DOB)) as [monthname],

year(DOB)as [year]

from datetimetable

DatePart, DateAdd and DateDiff:-

DatePart (datepart, date):- Returns an integer representing the specified DatePart. This function is similar to DateName (). DateName returns nvarchar, where as DatePart () returns an integer.

Ex- select DATEPART(WEEKDAY,'2016-01-31 02:25:11.1223452')

select DATEPART(MONTH,'2016-01-31 02:25:11.1223452')

Ex-

select DATENAME(WEEKDAY,'2016-01-31 02:25:11.1223452')

select DATENAME(MONTH,'2016-01-31 02:25:11.1223452')

DATEADD(DatePart, numberToAdd, date ):- Returns the datetime, after adding specified numberToAdd, to the DatePart specified of the given date.

Ex-  select DATEADD(DAY,31,'2016-01-31 02:25:11.122')

      select DATEADD(DAY,-20,'2016-01-31 02:25:11.122')

DATEDIFF (Datepart, startdate, enddate):- returns the count of the specified DatePart boundaries crossed between the specified startdate and enddate.

Syntax:-

Ex-

select DATEDIFF(YEAR,'05/06/1992','01/02/2016')

--it returns year

output:-

select DATEDIFF(MONTH,'05/06/1992','01/02/2016')

select DATEDIFF(DAY,'05/06/1992','01/02/2016')

select DATEDIFF(HOUR,'05/06/1992','01/02/2016')

Read More