To get the current system date and time in SQL Server, you will use the GETDATE() date function.

SELECT GETDATE() AS [CurrentDateTime]

GETDATE() returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.  Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.

The equivalent of Oracle's SYSDATE in SQL Server is the GETDATE() date function.

SELECT GETDATE() AS [SYSDATE]

Similar to Oracle's SYSDATE, GETDATE() returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.  Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.

There are 2 ways to add or subtract a certain number of days from a date.  The first option is to simply add or subtract the number of days you want using the addition (+) or subtraction (-) mathematical operators.  Here's an example on how to do it:

SELECT GETDATE() - 7 AS [SevenDaysAgo]
SELECT GETDATE() + 3 AS [ThreeDaysFromToday]

The other way is to use the DATEADD date function.  The DATEADD date function returns a new datetime value based on adding an interval to the specified date.

SELECT DATEADD(DD, -7, GETDATE())AS [SevenDaysAgo]
SELECT DATEADD(DD, 3, GETDATE()) AS [ThreeDaysFromToday]

The syntax of the DATEADD date function is as follows:

DATEADD ( datepart , number, date ) 

datepart is the parameter that specifies on which part of the date to return a new value.  For days, you can use either DD, D, or DAY.  number is the value used to increment datepart.  date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Regardless of which of these two methods is used, both of them will return a datetime value that includes the time part.

There are a couple of ways of extracting either the day, month or year parts from a datetime column.  The first method is the use of the DAY, MONTH and YEAR date functions.  The DAY date function returns an integer representing the day datepart of the specified date.  The MONTH date function returns an integer that represents the month part of a specified date.  Lastly, the YEAR date function returns an integer that represents the year part of a specified date.

The syntax of these date functions are as follows:

DAY ( date ) 
MONTH ( date ) 
YEAR ( date ) 

In all three date functions, the date parameter is an expression returning a datetime or smalldatetime value, or a character string in a date format.  Use the datetime data type only for dates after January 1, 1753.  Here's an example of how to use it to get the month, day and year of the current system date:

SELECT MONTH(GETDATE()) AS [CurrentMonth],
       DAY(GETDATE())   AS [CurrentDay],
       YEAR(GETDATE())  AS [CurrentYear]

The second method of extracting the day, month or year from a datetime data type is with the use of the DATEPART date function.  The DATEPART date function returns an integer representing the specified datepart of the specified date.

The syntax of the DATEPART date function is as follows:

DATEPART ( datepart , date ) 

The datepart is the parameter that specifies the part of the date to return.  For the day, you can specify either DAY, DD, or D.  For the month, you can specify either MONTH, MM, or M.  Lastly, for the year, you can specify either YEAR, YYYY, or YY.  The date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.  Use the datetime data type only for dates after January 1, 1753.

Here's an example of how to use the DATEPART date function to extract the month, day and year from the current system date:

SELECT DATEPART(MONTH, GETDATE()) AS [CurrentMonth],
       DATEPART(DAY, GETDATE())   AS [CurrentDay],
       DATEPART(YEAR, GETDATE())  AS [CurrentYear]

Choosing between the two methods, it is preferred to use the MONTH, DAY and YEAR date functions over the DATEPART date function simply because of simplicity of use.  The three functions take only 1 parameter while DATEPART takes 2.

In SQL Server, there is no single function that is the equivalent of Oracle's TRUNC function, which can be used to get just the date part of a datetime data type.  But there are different ways of getting just the date part of a datetime data type and this is discussed on the following link: 

Get Date Only User-Defined Function

To get just the time part of a DateTime data type, the CONVERT function can be used.  The CONVERT function explicitly converts an expression of one data type to another.  The syntax of the CONVERT function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression is any valid Microsoft® SQL Server™ expression. data_type is the target system-supplied data type.  length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types.  Lastly, style is the style of date format used to convert datetime or smalldatetime to character data.

To get the time part of a datetime data type, the style to use is 108, which will format the datetime expression to HH:MI:SS format.

SELECT CONVERT(VARCHAR(10), GETDATE(), 108) AS [CurrentTime]

If you want to include the millisecond (HH:MI:SS.MMM), the style to use is 114:

SELECT CONVERT(VARCHAR(13), GETDATE(), 114) AS [CurrentTime]

In both cases, the returned time part is of varchar data type.  If you want to return the time part as a datetime data type, a CAST needs to be performed:

SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 108) AS DATETIME) AS [CurrentTime]
SELECT CAST(CONVERT(VARCHAR(13), GETDATE(), 114) AS DATETIME) AS [CurrentTime]

Casting the time part to a datetime data type will include a date part with a value of 01/01/1900 in the result.  Here's an example:

CurrentTime
-------------------------
1900-01-01 09:30:25.000

To display a date in the MM/DD/YYYY format, you will be using the CONVERT function.  The syntax of the CONVERT function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression is any valid Microsoft® SQL Server™ expression. data_type is the target system-supplied data type.  length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types.  Lastly, style is the style of date format used to convert datetime or smalldatetime to character data.

To display the date in the MM/DD/YYYY format, the value to be passed to the style parameter will be 101:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

If today is December 25, 2000, the output of this will be:

MM/DD/YYYY
------------
12/25/2000

It should be noted that the output is now of VARCHAR data type and not DATETIME data type.

There's no built-in function in SQL Server that will return the first day of the month for any given date.  However it can easily be determined using the different date and mathematical functions that already exist in SQL Server.  The basic concept is simply replacing the day part of the given date with 1.  The following link discusses a few ways of getting the first day of the month:

Get First Day of the Month User-Defined Function

To get the difference in days between two dates, you will be using the DATEDIFF date function.  The DATEDIFF date function returns the number of date and time boundaries crossed between two specified dates and its syntax is as follows:

DATEDIFF ( datepart , startdate , enddate )

datepart is the parameter that specifies on which part of the date to calculate the difference.  Since we are looking for the difference in days, we will be passing a value of DAY, DD or D in this parameter.  startdate is the beginning date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.  enddate is the ending date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.

To get the number of days between the Fourth of July and Christmas day:

DECLARE @FourthOfJuly    DATETIME
DECLARE @Christmas       DATETIME

SET @FourthOfJuly = '2005/07/04'
SET @Christmas    = '2005/12/25'

SELECT DATEDIFF(DAY, @FourthOfJuly, @Christmas) AS [DateDifference]

The output of this script is 174 days.

A datetime data type is date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).  Values are rounded to increments of .000, .003, or .007 seconds

On the other hand, a smalldatetime data type is a date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute.  smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers.  The first 4 bytes store the number of days before or after the base date, January 1, 1900.  The base date is the system reference date.  Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime.  SQL Server stores smalldatetime values as two 2-byte integers.  The first 2 bytes store the number of days after January 1, 1900.  The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

smalldatetime is usually used when you don't need to store the time of the day such as in cases of effectivity dates and expiration dates.  datetime  is used if the time of the day is needed and up to the second accuracy is required.