SQL Server Basic – V

–Selecting columns based on a Where clause which is further constrained by an AND clause
SELECT * from Northwind..Customers
WHERE Phone like ‘(5)%’
AND RIGHT(Postalcode,2) = ’33’

–Selecting columns based on a Where clause which is further constrained by an AND clause
SELECT * from Northwind..Customers
WHERE Phone like ‘(5)%’
AND RIGHT(Postalcode,2) = ’33’
OR  RIGHT(Postalcode,2) = ’23’

–Selecting columns based on a Where clause which is further constrained by a BETWEEN clause
SELECT Au_LName, YTD_SALES
from Pubs..titleView
where ytd_sales between 2000 and 4000
order by 2

–Finding the Grouped Count based on several filtering criteria
Use Pubs
SELECT STOR_ID, COUNT(*) count
FROM sales
WHERE title_id like ‘PC%’
AND Stor_id between 7000 and 8000
OR ord_date between ‘1994-01-01’ and ‘1995-01-01′
GROUP BY STOR_ID

–GetDate( ) processing with integral days processing
select getdate(), getdate()-1, getdate() + 365

–GetDate( ) processing  with fractional days increment
select getdate() + 1.5
go
select getdate() + 1.45
go

–DateAdd( ) function processing
select hire_date,dateadd(yy,2,hire_date)
from pubs..employee

select hire_date,dateadd(yyyy,2,hire_date)
from pubs..employee

–DateAdd( ) function processing for year increments
select ’15-Aug-1930′ birth_day,dateadd(yy,60,’15-Aug-1930′) retirement_day

–DateAdd( ) function processing for various input date formats
select dateadd(yy,60,’1930-12-13′),
dateadd(yy,60,’1930.12-13′) ,
dateadd(yy,60,’Dec,13 1930′)

–DateAdd( ) function processing for days & weeks increments
select dateadd(dd,-365,hire_date)
from pubs..employee
go

select dateadd(ww,1000,hire_date)
from pubs..employee
go

–DateAdd( ) function processing for months, minutes and seconds increments
Select DATEADD(mm,-3,getdate( ))

Select DATEADD(ss,86400,getdate( )) as Tommorow_Now

Select RIGHT(DATEADD(mi,150,getdate( )),8) as TestEndTime

–Date Conversion formats- Specifying SQL Output formats
select convert(varchar,getdate(), 1)          –Output in mm/dd/yy

select convert(varchar,getdate(), 2)          — Output in yy.mm.dd

select convert(varchar,getdate(), 3)          — Output in dd/mm/yy

select convert(varchar,getdate(), 4)          — Output in dd.mm.yy

select convert(varchar,getdate(), 5)          — Output in dd-mm-yy

select convert(varchar,getdate(), 6)          — Output in dd Mon yy

select convert(varchar,getdate(), 7)          — Output in Mon dd, yy

select convert(varchar,getdate(), 8)          — Output in hh+:mi:ss

select convert(varchar,getdate(), 9)          — Output in Mon dd yyyy hh:mi:ss:mmmXM

select convert(varchar,getdate(), 10)       — Output in mm-dd-yy

select convert(varchar,getdate(), 11)       — Output in yy/mm/dd

select convert(varchar,getdate(), 12)       — Output in yymmdd

select convert(varchar,getdate(), 13)       — Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(), 14)       — Output in hh+:mi:ss:mmm

–Date Conversion formats in extended Year notation – specifying SQL Output formats
select convert(varchar,getdate(), 101)     — Output in mm/dd/ yyyy

select convert(varchar,getdate(),102)      — Output in yyyy.mm.dd

select convert(varchar,getdate(),103)      — Output in dd/mm/yyyy

select convert(varchar,getdate(),104)      — Output in dd.mm.yyyy

select convert(varchar,getdate(),105)      — Output in dd-mm-yyyy

select convert(varchar,getdate(),106)      — Output in dd Mon yyyy

select convert(varchar,getdate(),107)      — Output in Mon dd, yyyy

select convert(varchar,getdate(),108)      — Output in hh+:mi:ss

select convert(varchar,getdate(),109)      — Output in Mon dd yyyy hh:mi:ss.mmmXM

select convert(varchar,getdate(),110)      — Output in mm-dd-yyyy

select convert(varchar,getdate(),111)      — Output in yyyy/mm/dd

select convert(varchar,getdate(),112)      — Output in yyyymmdd

select convert(varchar,getdate(),113)      — Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(),114)      — Output in hh+:mi:ss:mmm

–Date Conversion formats – specifying Input formats for the varchar dates
select convert(datetime,’12/11/1978′,101)              –Input in mm/dd/ yyyy

select convert(datetime,’1978.12.11′,102)                –Input in yyyy.mm.dd

select convert(datetime,’11/12/1978′,103)              –Input in dd/mm/yyyy

select convert(datetime,’11.12.1978′,104)                –Input in dd.mm.yyyy

select convert(datetime,’11-12-1978′,105)               –Input in dd-mm-yyyy

select convert(datetime,’11 Dec 1978′,106)             –Input in dd-mm-yyyy

select convert(datetime,’Dec 11,1978′,107)             –Input in Mon dd, yyyy

select convert(datetime,’12-11-1978′,110)               –Input in mm-dd-yyyy

select convert(datetime,’1978/12/11′,111)              –Input in yyyy/mm/dd

select convert(datetime,’19781211′,112)  –Input in yyyymmdd

–Date Conversion formats- Input Date format approximation for centuries.
select convert(datetime,’11 Dec 78′,6)      –Input in dd Mon yy

select convert(datetime,’11 Dec 03′,6)      –Input in dd Mon yy

–Date Conversion formats- Input Date format , longest forms and for timestamps .

select convert(datetime,’Dec 11 1978 22:12:12.121′,113)    –Input in Mon dd yyyy hh+:mi:ss:mmm

select convert(datetime,’Dec 11 1978 10:12:12.121PM’,109) –Input in Mon dd yyyy hh:mi:ss.mmmXM

select convert(datetime,’22:12:12.121′,114)             –Input in hh+:mi:ss:mmm

select convert(datetime,’22:12:22′,108)     –Input in hh+:mi:ss

–Date Conversion formats- Input Date format , longest forms and for timestamps .
select * from pubs..employee
where convert(datetime,hire_date,6) > ’11 Dec 93′

select * from pubs..employee where hire_date > ’11 Dec 93′

–Date Conversion formats- Input Date format , longest forms and for timestamps .
select ‘VERIFIED’
where convert(datetime,’11 Dec 93′,6) = convert(datetime,’19931211′,112)

–Date Conversion formats- the ODBC format .
select convert(varchar,hire_date, 121) from pubs..employee where hire_date > ’11 Dec 93′

select substring(convert(varchar,getdate(), 120),12,24)

–Small DateTime format
select getdate(), convert(smalldatetime,getdate( ))

–Days elasped since ‘1.1.1900’
select getdate(),convert(decimal,getdate())

select convert(int,getdate())-convert(int,hire_date) DaysInJob from pubs..employee

–Milliseconds elasped since ‘1.1.1900’

select replace(convert(varchar,getdate(), 114),’:’,”) — stripped time from hh+:mi:ss:mmm

select convert(varchar,getdate(), 112)+replace(convert(varchar,getdate(), 114),’:’,”)

–Unique string generation based on time elasped in milliseconds(?) from absolute zero.
select convert(timestamp,getdate())
go

–Finding the difference in two dates
select datediff(dd,hire_date,getdate()) EmploymentINDays from pubs..employee

select datediff(mm,hire_date,getdate()) EmploymentINMons from pubs..employee

select datediff(yy,hire_date,getdate()) EmploymntINYers from pubs..employee

select datediff(ww,hire_date,getdate()) EmploymntINWeeks from pubs..employee

select datediff(mi,hire_date,getdate()) EmploymntINMinutes from pubs..employee

–Finding Anniversaries in this year
select dateadd(yy,datediff(yy,hire_date,getdate()),hire_date) JOBANNIVERSARY
from pubs..employee
GO
select convert(varchar(6),
dateadd(yy,datediff(yy,hire_date,getdate()),hire_date)) JOBANNIVERSARY
from pubs..employee
GO

–Finding Job Lengths or duration from a fixed past date
select *,datediff(yy,hire_date,getdate()) AS JobYears
from pubs..employee
where datediff(yy,hire_date,getdate()) > 14

–Finding the Year, Month & Date parts from a date expression
select DAY(hire_date) from employee
select MONTH(hire_date) from employee
select YEAR(hire_date) from employee

–Date constituent functions properties
SELECT MONTH(0), DAY(0), YEAR(0)

–DatePart functions
select DATEPART(dw,hire_date) from employee — Day of week when Joining

select DATEPART(dy,hire_date) from employee — Nth Day of Year on Joining

select DATEPART(qq,hire_date) from employee — Quarter of Year at Joining

select DATEPART(mm,hire_date) from employee — Month of Year at Joining

select DATEPART(dd,hire_date) from employee — Day of Month in the year of Joining

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s