System functions are inbuilt functions that can be used to perform some tasks without writing code to perform the task. There are lot of system functions but l’m going quickly over just couple of them.
–Date/Time functions
–static dates are stored as ‘yyyy-mm-dd’
There are many date formats but l’m just dealing with one basic one as shown above.
select GETDATE()–retrieves the current date and time info
select YEAR(‘1998-02-01’)— returns the year part of the date
select YEAR(GETDATE())—returns the year part of the current date
select MONTH(GETDATE())—returns the month part of the current date
select DAY(GETDATE()) — returns the day part of the current date
select WEEKDAY(GETDATE()) — returns a number from 1 – 7 indicating the day of the current date. The numbering starts from 1 for sunday and so on.
–Datepart function is generic and can be used to extract any part of a date
select DATEPART(YEAR,GETDATE()) –Get the year part of the current date
select DATEPART(minute,GETDATE())– Get the minute part of the current date
select DATEPART(hour,GETDATE()) — Get the hour part of the current date
select DATEPART(SECOND,GETDATE())– Get the seconds part of the current data
–String functions
LEN(string) — returns the length of the string
UPPER(string)–returns the upper case form of the string
LOWER(string)–returns the lower case form of the string
LTRIM(‘ xxxxxx’)–remove the blanks on the left
RTRIM(‘xxxxxx ‘)–remove the blanks on the right
LTRIM(rtrim(‘ xxxxxx ‘))–remove blanks on left and right
LEFT(string,3)–Returns the left 3 characters of the string
RIGHT(string,2),–Returns the right 2 characters of the sting
REVERSE(string)–Reverses the string
REPLACE ( string, string_pattern , string_replacement )- This replaces the string with an original pattern with the new pattern.
SUBSTRING– use to extract portion of a string.
SUBSTRING(String, startpos,number of chars)
CHARINDEX– find starting position of a parten within a string. returns 0 if not found.
CHARINDEX(string_pattern,string_expression)
SELECT CHARINDEX(‘ni’, ‘california’)—returns 8
SELECT CHARINDEX(””,’O”niel’) — returns 2
SELECT SUBSTRING(‘California’,CHARINDEX(‘f’,’california’),3)
SELECT SUBSTRING(string, 1,3) as ‘Left 3 chars’
FROM person
–Data conversion functions
–CAST(column to convert as datatype)
–CONVERT(datatype, column)
SELECT CAST(123 as varchar(5)) + ‘a’
SELECT CONVERT(varchar(5),123) + ‘a’
SELECT CAST(123 as numeric(10,2))
SELECT CAST(GETDATE() as Date)
SELECT CONVERT(DATE, GETDATE())
–convert allows to display dates in diffferent formats but cast can not.
select Convert(varchar(50), GETDATE())
select Convert(varchar(50), GETDATE(),101)
select Convert(varchar(50), GETDATE(),107)
It’s very good to look at the MSDN on all functions for performing basic stuff.
Keep in touch, we’ll be going over Joins in the next tutorial.
