SQL Server 2012 RC0, new functions: DATE & TIME FROMPARTS

We have a series of new function in SQL Server 2012 RC0. 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 RC0, 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 RC0

Microsoft SQL Server 2012 Release Candidate 0 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.

blog.mssqlserver.se revisited part 2 of {We'll see...}

As said, I was in Berlin at TechEd when I realized that I actually killed my blog. Well, my first thought was that I should restore the old one. My descision was instead to install a new version. After install I had it up and running, and started to write blogentries. All was fine and set. About a year later I decided to upgrade that server with more RAM, and fresh software. Started out doing all backups and such. I was feeling safe and did a fresh install of Windows Server 2008 R2. While at it I was thinking of changing everything. The story is that I use this server mainly for my virtual labs. And to host the blog. It's not my primary webhost, I have that stuff located in another location. With change management and all. Fully secured, and all by the book. This server instead is my playground. TMG sounded like a great idea, and it was here my problems started. TMG is a bit more than just ISA Server, or Proxy Server, that I know alot about. After first try, I understood that courses are great. Especially courses on the product you're about to work with. As the priviliged I am as a Microsoft Certified Trainer I went to Courseware Library and didn't find anything so I tried again...

blog.mssqlserver.se revisited part 1 of {We'll see...}

It's been a while I had my blog up and running. This is a true story, names have been changed to protect the individuals.

It all started when I was at TechEd Europe in Berlin 2010. Before I left for Germany I started an update of my blog engine, let us just call it "my blog engine". This was running on a virtualizing platform of a brand I leave out, well it was in Beta 2.0 actually. I accidently forgot to add the license key for the platform when I was setting it up (upsetting perhaps?). To add for "the stew of misery" my hosting partner forgot mentioning a small upgrade that was about to happen. The rack(or racks actually) where my servers were secured was about to be well connected against the new and alot of nines more highly available external power supply. Well, the list actually continues a bit further and I guess you all get the picture.

Said and done. While I was working in my booth, as a representative for MCT Europe, I wanted to update the blog with an answer for a question I just got. I opened up IE on my laptop and entered the URL, http://blog.mssqlserver.se, and pressed return. A few seconds later I ended up in the Point Of No Return. No reply from my webserver?
To be continued... *smiles