Monday, April 8, 2013

scalar functions

--scalar  function
--operators 
use communityassist
Select 5 * 2 /3 + 4

-- math operators
Select DonationAmount, DonationAmount * .78 as ToCharity from Donation

--concatination
Select Lastname + ', ' + firstname "Name" from Person

--Date time functions
Select * From Donation
Select  Distinct Month(DonationDate) [Month] From Donation
Select Day(DonationDate) [Day] From Donation
Select Year(DonationDate) [Year] from Donation
Select DatePart(yy,DonationDate) from Donation
Select DatePart(mm,DonationDate) from Donation
Select GetDate() as Today
Select DateAdd(yy,5,DonationDate) [add 5 years] from Donation

Select distinct Year(DateAdd(yy, 5,DonationDate)) [Year],
Month(DateAdd(mm,3,DonationDate)) [Month] from Donation

Select cast(Month(donationDate) as nvarchar) + '/' 
+ cast(day(donationDate)as nvarchar) + '/' + cast(year(DonationDate)as nvarchar)
as [Date]
From donation

Select * From personContact

Select contactinfo, '(' + substring(Contactinfo, 1,3) + ')' 
+ substring(ContactInfo, 4,3) + '-' + Substring(ContactInfo, 6, 4) as Phone
From PersonContact
Where not ContactTypeKey =6

Select * from PersonAddress 

Select street, Substring(street, 1, charindex(' ',Street,1)) from PersonAddress

Select * from ContactType

Select upper(Street) From PersonAddress
Select lower(Street) From PersonAddress

Select donationDate,
 case Month(DonationDate) 
 when 2
  then 'February'
 when 3
  then 'March'
 when 4
  then 'April'
 else
  'Sometime'
 end
 as Month
 From Donation

 Select * From PersonAddress
 Select Street, Coalesce(Apartment, 'N/A') from PersonAddress

No comments:

Post a Comment