DATE FUNCTIONS IN SQL SERVER

DATE FUNCTIONS

getdate(): returns current system date & time

Eg: select getdate()

day(): returns day part from the date

Eg: select day(getdate()

select day('10/24/78')

Month(date)

returns month part from the date

Eg: 

select month(getdate())

select month('10/24/78')

Year():

Returns year part of the date.

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Eg:

select year('10/24/78')

select year(getdate())

Display employee records joined in leap year

select * from emp where year(hiredate)%4=0

Display employee records between January and April.

select * from emp where month(hiredate) between 1 and 4

Display employee records who are joined in first 15 days of January month in year 1981

select * from emp where day(hiredate) between 1 and 15 and month(hiredate)=1 and year(hiredate)=1981

DatePart(part,date)

It is used to extract specified part from the given date.

select datepart(dd,getdate())

o/p-7    (Day of the month)

     

select datepart(mm,getdate())

o/p-10    (month of the year)

select datepart(yy,getdate())

o/p-2013   (year of the date)

select datepart(dw,getdate())

o/p -2    (day of the weak)

select datepart(ww,getdate())

o/p-41   (weak of the year)

select datepart(qq,getdate())

o/p-4   (quarter of the year)

select datepart(hh,getdate())

o/p-22   (hours of the date)

select datepart(ss,getdate())

o/p-19   (seconds of the date)

select datepart(mi,getdate())

O/p-52   (minutes of the date)

DateName()

This function is same as date part expect that while extracting month and day of the week it will return month name and weak name instead of Name.

given date.

select datename(dd,getdate())

o/p-7    (Day of the month)

     

select datename (mm,getdate())

o/p-october    (month of the year)

select datename (yy,getdate())

o/p-2013   (year of the date)

select datename (dw,getdate())

o/p -Monday    (day of the weak)

select datename (ww,getdate())

o/p-41   (weak of the year)

select datename (qq,getdate())

o/p-4   (quarter of the year)

select datename (hh,getdate())

o/p-22   (hours of the date)

select datename (ss,getdate())

o/p-19   (seconds of the date)

select datename (mi,getdate())

O/p-52   (minutes of the date)

Display smith joined on Sunday as outpu

select ename + ' joined on ' +datename(dw,hiredate) from emp

   

DateAdd()

It is used to add or subtract n no of specified parts to the given date and return a date.

Add 6 days to the getdate

select DATEADD(dd,6,getdate())

output:2013-10-18 09:52:49.720

Add 6 months to the getdate

select DATEADD(mm,6,getdate())

output: 2014-04-12 09:53:42.340

Subtract 6 days to the getdate.

select DATEADD(dd,-6,getdate())

output: 2013-10-06 09:54:41.340

DateDifference()

It is used to get the difference between the two different dates in specified format.

Select datediff(dd,'03/01/2012',getdate())

Output: 590

Select datediff(mm,'03/01/2012',getdate())

Output: 19

Select datediff(ww,'03/01/2012',getdate())

Output:84

getUTCDate()

Returns the date time value representing the current UTC time(Coordinated universal Time or Greenwich Mean Time).

Select GETUTCDATE()

Find the employees who are joined in the company in Dec Month of every year.

select * from emp where datepart(mm,hiredate)=12

or if you want only 2013 year

than write the query as

select * from emp where datepart(mm,hiredate)=12 and datepart(yy,hiredate)=2013

Find the programmer whose DOB is on system date.

select * from PROGRAMMER where datepart(dd,dob)=datepart(dd,getdate()) and datepart(mm,dob)=datepart(mm,getdate())

PREDICATES OR SPECIAL OPERATORS

PREDICATES OR SPECIAL OPERATORS

DEPT TABLE

EMP TABLE

Every operator that is used within the conditions in " where clause" is called as predicates.

Tsql provides special predicated to simplify the conditions in where clause which are collectively called as “predicates”

a.          Between

b.          Not between

c.          In

d.          Not in

e.          Is null

f.          Is not null

g.          Like

h.          Not like

Between:

Is used to verify wheather or not the given value is within the specified range.

Between operator always works with lower limit & upper limit but not upper limit & lower limit

Find the employee whose salary is in the range of 2000 and 4000

select * from emp where sal between 2000 and 4000

To display the employees who joined in the company in the year 1982

select * from emp where hiredate between '1/1/1982' and '12/31/1982'

Find the employees whose salary is not in the range of 2000 and 4000

select * from emp where sal not between 2000 and 4000

IN OPERATOR

This operator is used to verify whether or not the given value is within a specified list of values

Examples

Find the employees whose salary is either 800 or 950 or 1600

Select * from emp where sal in (800,950,1600)

Find the employees whose job is either clerk or manager

Select * from emp where job not in('clerk','manager')

Display employee list who are not working for 10 or 20 the department.

Select * from emp where deptno not in(10,20)

Find the employees whose salary is not either 800 or 950 or 1600

Select * from emp where sal not in(800,950,1600)

Display employee records earning between 2000 and 5000 and earning multiple of 50 and working as clerk or manager & not deptno in 10 and 20

select * from emp where sal between 2000 and 5000

and sal%50=0

and job in ('clerk','manager')

and deptno  not in(10,20)

==================================================

LIKE

It is used to search for a particular pattern within a column

While working with like predicates you have to use two wild card characters

1 %--> it is used for representing multiple characters.

2 _underscore: it is used for representing single character.

Find the employees whose name starts with alphabets s

select * from emp where ename like 's%'

Name ends with s

select * from emp where ename like '%s'

Name starts with s and ends with s

select * from emp where ename like 's%s'

Find the employees whose name contains alphabet a

select * from emp where ename like '%a%'

Find the employees whose name contains alphabet a minimum twice in the name

select * from emp where ename like '%a%a%'

Find the employees whose name contains alphabet L as second letter in the name

select * from emp where ename like '_l%'

Display employee records name starts between a and p

select * from emp where ename like'[a-p]%'

where name doesnot start with a and p

select * from emp where ename not like'[a-p]%'

select * from emp where ename like '[^a-p]%'

Display employee records name starts with a or b or s or t

select * from emp where ename like '[abst]%'

Find the employees whose name contains the alphabet I or R OR N as 3 letter in the name

select * from emp where ename like '__[i,r,n]%'

Find the employees whose name contains any alphabet in the range of a to k as 2 letter in the name

select * from emp where ename like '_[a-k]%'

Find the employees whose name does not contain alphabet A as second letter in the name.

select * from emp where ename like '_[^a]%'

Find the employees whose name does not start with alphabet s

select * from emp where ename not like 'S%'

Find the employee whose name contain alphabet L as second letter in the name but not alphabet e as last but one character in the name

select * from emp where ename like '_l%[^e]_'

Isnull

Is used to search for null in a column

Find the employees who are not drawing commissions

select * from emp where COMM is null

Find the employee who have no manager

select * from emp where mgr is null

Find the employees who  are drawing commission

select * from emp where comm is not  null

Isnull function in SQL SERVER

Isnull function

Any arthimetic operation with null results in null. Hence whenever you have to perform arithmetic operations on a column containing null use the function “ISNull”

DEPT TABLE

EMP TABLE

Display empno,ename, sal, comm along with total and annual salary

select empno,ename,sal,comm ,sal+isnull(comm,0),(sal+isnull(comm,0))*12 from emp

OR BY USING COL ALIAS

select empno,ename,sal,comm ,sal+isnull(comm,0) AS "TOTAL SALARY",(sal+isnull(comm,0))*12 AS "ANNUAL SALARY"from emp

Distict KeyWord

Distict KeyWord

It is used to eliminate duplicate values in the output of select statement.

the role for using distinct keyword is the keyword distinct can be used only once that also immediately next to the keyword select

Tables Dept table

Emp table

Display the list of department no in which employees are working

Select distinct deptno from emp

Display the list of jobs available in every Dept 
select distinct deptno,job from emp order by deptno

When you select multiple columns while using the keyword distinct then duplicates will not be eliminated in individual columns and duplicates will be eliminated in the combination of columns you select.

Eg: select distinct job from emp

select distinct deptno,job from emp

ORDER OF DECLARATION IN SQL SERVER

Tables

ORDER OF DECLARATION

(USER FOLLOWS THIS ONE)

MEANS WHICH CLAUSE

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

ORDER OF EXECUTION (SYSTEM FOLLOWS)

FROM

WHERE

GROUP BY

HAVING

SELECT

Display employee records earning more than 2000

select * from emp where sal >2000

Display employee records earning more than 2000 and less than 5000

select * from emp where sal>2000 and sal<5000

Order by Clause

It is used to sort rows retrieved by the “select statement” in Ascending Order or Descending Order.

Syntax

Select */<collist>  from <tablename> [where condition] order by <colname> [asc,desc],

<colname> [asc,desc]

Default is ascending order

Display the details of employee in the order of highest salary to the lowest salary

select * from emp order by sal desc

     

Display the details of employee working in dept 30 in alphabetical order

select * from emp where deptno=30 order by ename

Display the details of employees in order of highest salary to lowest salary and arrange the employee who are drawing same salary.

select * from emp order by sal desc , ename asc

with in order by clause you can specify multiple columns when you specify the columns in order by then first it will arrange the rows in the specified order of first column and then only the rows that have same value in that first column will be arranged in the specified order of the second column.

Sorting the emp table in ename wise

Select * from emp order by ename

Or 

select * from emp order by 2

Arrange employee records department wise with in the department salary wise

Select deptno,ename,sal,job from emp order by deptno, sal desc

Display the employee records for 10 or 20 deptno and sort the result salary-wise in descending order

Select * from emp where deptno in(10,20) order by sal desc