5/25/12

Conferences to look for Autumn 2012

There are some nice SQL conferences upcoming:
SQLTuneIn 2012 in Zagreb, Croatia, 24-25 of September
http://www.sqltunein2012.com/

PASS SQLRally Nordic 2012 in Copenhagen, Denmark, 1-3 of October
http://www.sqlpass.org/sqlrally/2012/nordic/

And then it's:
Microsoft TechEd EMEA in Amsterdam, Netherlands, 26-29 of June
http://europe.msteched.com/

MCT Summit Europe in Warsaw, Poland, 6-8 of September
http://www.mctsummit.eu/

SEF 2012 in Stockholm, Sweden, 22-23 of October
http://www.seforum.se

And don't miss
Microsoft Sommarkollo 2012, Sweden, Summer of 2012
http://www.microsoft.com/sv-se/sommarkollo/default.aspx

I'll probably be on most of these as a speaker or other official role, if you're plan to visit one or more of these conferences. Check out this blog to see if I'm there aswell.

3/8/12

SQL Server 2012 goes RTM

A few weeks ago the last golden bits of SQL Server 2012 were finally signed ready. Yesterday, or today, depending on where on earth you are the launch of the newest, finest version of SQL Server ever.

Download from here: Try SQL Server 2012 RTM
Experience the SQL Server Virtual Launch here: Party

Back from my first ever MVP Summit

I'd like to direct a huge thank you to the SQL Server Product Group, my MVP Lead Willian Jansen, the MVP Awards team, and the community for giving me the opportunity of being a MVP on SQL Server: Architecture. The summit rocked, and I will be back if you want me too.

1/9/12

SQL Server 2012, new functions: DATE & TIME FROMPARTS

We have a series of new function in SQL Server 2012. Within the Date and Time area we got both the EOMONTH and a whole bunch of date and/or time from parts functions.

SMALLDATETIMEFROMPARTS( year, month, day, hour, minute )
DATETIMEFROMPARTS( year, month, day, hour, minute, seconds, milliseconds )
DATETIME2FROMPARTS( year, month, day, hour, minute, seconds, fractions, precision )
DATETIMEOFFSETFROMPARTS( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
DATEFROMPARTS( year, month, day )
TIMEFROMPARTS( hour, minute, seconds, fractions, precision )

When is this needed?

If you store date and time with integers, one column per part, take a look at the example below.

DECLARE @table TABLE (
iYear int,
iMonth int,
iDay int,
iHour int,
iMinute int,
iSecond int,
iMilliseconds int,
iFractions int );
DECLARE @timestamp datetime2(7);
SELECT @timestamp = sysdatetime();
INSERT INTO @table(iYear, iMonth, iDay, iHour, iMinute, iSecond, iMilliseconds, iFractions)
SELECT
DATEPART(yyyy,@timestamp), -- Years
DATEPART(mm,@timestamp), -- Months
DATEPART(dd,@timestamp), -- Days
DATEPART(hh,@timestamp), -- Hours
DATEPART(mi,@timestamp), -- Minutes
DATEPART(ss,@timestamp), -- Seconds
DATEPART(ms,@timestamp), -- Milliseconds
DATEPART(ns,@timestamp)/100;
-- The fraction, amount of nanoseconds divided by 100
-- Test the functions
SELECT SMALLDATETIMEFROMPARTS(iYear, iMonth, iDay, iHour, iMinute) FROM @table -- SMALLDATETIME
SELECT DATETIMEFROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iMilliseconds) FROM @table -- DATETIME
SELECT DATETIME2FROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iFractions, 7) FROM @table -- DATETIME2
SELECT DATETIMEOFFSETFROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iFractions, 1, 0, 7) FROM @table -- DATETIMEOFFSET
SELECT DATEFROMPARTS(iYear, iMonth, iDay) FROM @table -- DATE
SELECT TIMEFROMPARTS(iHour, iMinute, iSecond, iFractions, 7) FROM @table -- TIME

SQL Server 2012, new functions: EOMONTH

Have you missed the Oracle function LAST_DAY? Have you solved it in Microsoft SQL Server in various ways?

What we could do before the upcoming release of SQL Server is to start out with the current date, add one month, a8nd subtract the number of days of the current month. Or we could get the current date, add one month, convert that to characters, cut out the year and month part and concatenate that with the string '801', and convert that to date, and then subract one day.

Look no further, now we have the EOMONTH-function instead.

EOMONTH ( start_date [, month_to_add ] ) * start_date is the current date, or the date you want the highest date for * month_to_add is the optional increment of months

-- Prepare a variable with the current date
DECLARE @date date;
SELECT @date = getdate();

-- Method 1
SELECT DATEADD(day, (0 - datepart(day, @date)), DATEADD(month, 1, @date));

-- Method 2
SELECT dateadd(day, -1, CAST(CONVERT(varchar(8), dateadd(month, 1, @date), 120) + '01' AS date));

-- New function
SELECT EOMONTH(@date);
SELECT EOMONTH(@date, 2);
SELECT EOMONTH(@date, -4);

New and changed functions in SQL Server 2012

Microsoft SQL Server 2012 introduces 14 new built-in functions. These functions ease the path of migration for information workers by emulating functionality that is found in the expression languages of many desktop applications. However these functions will also be useful to experienced users of SQL Server.

The new functions are:


Conversion functions
* PARSE
* TRY_CONVERT
* TRY_PARSE

Date and time functions
* EOMONTH
* SMALLDATETIMEFROMPARTS
* DATETIMEFROMPARTS
* DATETIME2FROMPARTS
* DATETIMEOFFSETFROMPARTS
* DATEFROMPARTS
* TIMEFROMPARTS

Logical functions
* CHOOSE
* IIF

String functions
* CONCAT
* FORMAT

In addition to the 14 new functions, one existing function has been changed. The existing LOG function now has an optional second base parameter.

I will try to cover them over the next upcoming blogposts. I start right away with Date and time functions in the next two posts.