Archive for the ‘SQL Server 2008’ Category

Compound Assignment Operators

Posted: November 21, 2011 in SQL Server 2008

Compound assignment operators help abbreviate code that assigns a value to a column or a variable. The new operators are::

-- Declare variables
DECLARE @i INT = 50;

-- Plus equals
SET @i += 10; 
SELECT @i;

-- Minus equals
SET @i -= 10; 
SELECT @i;

-- Multiplication equals
SET @i *= 10; 
SELECT @i;

-- Division equals
SET @i /= 10; 
SELECT @i;

-- Division equals
SET @i %= 10; 
SELECT @i;

DATETIME Functions

Posted: November 21, 2011 in SQL Server 2008

The following DATETIME functions are available:

-- Returns the current database system timestamp as a DATETIME value without the database time zone offset
-- This value is derived from the operating system of the computer on which the instance of SQL Server is running
SELECT GETDATE();

-- Returns a DATETIME2(7) value that contains the date and time of the computer on which the instance of SQL Server is running
SELECT SYSDATETIME();

-- Returns a DATETIMEOFFSET(7) value that contains the date and time of the computer on which the instance of SQL Server is running
-- The time zone offset is included
SELECT SYSDATETIMEOFFSET();

-- Returns a DATETIME2 value that contains the date and time of the computer on which the instance of SQL Server is running
-- The date and time is returned as UTC time (Coordinated Universal Time)
-- The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits
SELECT SYSUTCDATETIME();

-- Returns a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset
SELECT SWITCHOFFSET('1998-09-20 7:45:50.71345 -5:00',  '-08:00');

-- Returns a DATETIMEOFFSET value that is translated from a DATETIME2 expression
DECLARE @today DATETIME2;
SELECT @today = GETDATE();
SELECT TODATETIMEOFFSET(@today, '-07:00');

-- Returns the current database system timestamp as a DATETIME value without the database time zone offset
-- This value is derived from the operating system of the computer on which the instance of SQL Server is running
SELECT CURRENT_TIMESTAMP;

-- Returns an integer that represents the year of the specified date
SELECT YEAR(GETDATE());

-- Returns an integer that represents the month of the specified date
SELECT MONTH(GETDATE());

-- Returns an integer representing the day (day of the month) of the specified date
SELECT DAY(GETDATE());

-- Returns the current database system timestamp as a DATETIME value
-- The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time)
-- This value is derived from the operating system of the computer on which the instance of SQL Server is running
SELECT GETUTCDATE();

DATETIMEOFFSET Datatype

Posted: November 21, 2011 in SQL Server 2008

This datatype defines a date that is combined with a time of a day that has time zone awareness. This is based on a 24-hour clock and can be used as follows:

-- Declare variables
DECLARE @dt  DATETIMEOFFSET(0) = '2007-10-29 22:50:55 -1:00';
DECLARE @dt1 DATETIMEOFFSET(0) = '2007-10-29 22:50:55 +5:00';

-- Get the hour difference
SELECT DATEDIFF(hh, @dt, @dt1);

DATETIME2 Data Type

Posted: November 21, 2011 in SQL Server 2008

You can now return larger fractional seconds and year range than the existing DATETIME datatype. You have the option of specifing the number of fractions. This can be between 0 and 7 and you can get the values as follows:

-- Declare variables
DECLARE @dt0 datetime2(0);
DECLARE @dt1 datetime2(1);
DECLARE @dt2 datetime2(2);
DECLARE @dt3 datetime2(3);
DECLARE @dt4 datetime2(4);
DECLARE @dt5 datetime2(5);
DECLARE @dt6 datetime2(6);
DECLARE @dt7 datetime2(7);

-- Get current date
SET @dt0 = GETDATE();
SET @dt1 = GETDATE();
SET @dt2 = GETDATE();
SET @dt3 = GETDATE();
SET @dt4 = GETDATE();
SET @dt5 = GETDATE();
SET @dt6 = GETDATE();
SET @dt7 = GETDATE();

-- Select value
SELECT @dt0;
SELECT @dt1;
SELECT @dt2;
SELECT @dt3;
SELECT @dt4;
SELECT @dt5;
SELECT @dt6;
SELECT @dt7;

TIME Data Type

Posted: November 21, 2011 in SQL Server 2008

You can now just store the time part of a DATETIME value as follows:

-- Declare variables
DECLARE @dt TIME;

-- Get current date
SET @dt = GETDATE();

-- Select value
SELECT @dt;

DATE Data Type

Posted: November 20, 2011 in SQL Server 2008

You can now just store the date part of a DATETIME value as follows:

-- Declare variables
DECLARE @dt DATE;

-- Get current date
SET @dt = GETDATE();

-- Select value
SELECT @dt;

C Style Math Syntax

Posted: November 20, 2011 in SQL Server 2008

You can now use C style math syntax as follows:

-- Declare variables
DECLARE @i INT = 0;

-- Set variable
SET @i += 10;

-- Select value
SELECT @i;