Programação

COMANDOS NOVOS select OrderId, OrderDate from Orders where Month(OrderDate) = 07

select OrderId, OrderDate from Orders where Day(OrderDate) = 15

select OrderId, OrderDate from Orders where year(OrderDate) = 1996

select OrderId, OrderDate from Orders where OrderDate >= '01/30/1997' --mês, dia, ano

select LTRIM(contactname) from Customers --REMOVE ESPACO ESQUERDA

select RTRIM(contactname) from Customers -- REMOVE ESPACO DIREITA

select UPPER(contactname) from Customers -- MAIUSCULO

select LOWER(contactname) from Customers -- MINUSCULO

SELECT CONVERT(DECIMAL(10,2), AVG(Freight)) AS Media From Orders
-- Faz a conversao para 10 digitos de precisao e 2 casas decimais

select OrderId, datediff(yy, OrderDate, getdate()) from Orders

select OrderId, datediff(dd, OrderDate, getdate()) from Orders

select OrderId, datediff(mm, OrderDate, getdate()) from Orders

select OrderId, datediff(wk, OrderDate, getdate()) from Orders

/* Dia (dd) dia de ano (dy)
Hora (hh)
Milissegundo (ms) minuto (mi) mês (mm) quarto (oq) segundo (ss) semana (wk)
Dia da Semana (dw) ano (yy) */

select substring(contactname,1,10) from Customers
--seleciona parte de uma string, a partir do primeiro caracter
--busca 10 caracteres para exibicao

select contactname, len(contactname) as 'caracteres'from Customers --mostra qtde caracteres de uma string select contactname, reverse(contactname) as 'Nome Invertido'from Customers
--inverter determinada string

select contactname, right(contactname, 5) as '5 ultimos'from Customers -- exibe 5 ultimos caracteres

select contactname, left(contactname, 5) as '5 primeiros'from Customers -- exibe 5 primeiros caracteres

select regiondescription, replace(regiondescription, 'Northern', 'Norte') from region
-- substitui

SELECT OrderId, Freight From Orders

SELECT OrderId, floor(Freight) From Orders
--remove a parte decimal

SELECT OrderId, Ceiling(Freight) From Orders