SQL SERVER – Denali – Date and Time Functions – DATEFROMPARTS() – DATETIMEFROMPARTS() – DATETIME2FROMPARTS() – TIMEFROMPARTS() – SMALLDATETIMEFROMPARTS() – DATETIMEOFFSETFROMPARTS() – A Quick Introduction

In SQL Server Denali, there are seven new datetime functions being introduced, namely:

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

Today we will quickly look at six of the above functions. I really wanted to write a separate blog post for all of them but once you see example here you will understand why they are very simple. First six functions have one thing in common among them. They all build datetime value from parameters passed into the function. Let us see the quick example.

SELECT DATEFROMPARTS (2010,12,31) AS Result;
SELECT DATETIME2FROMPARTS (2010,12,31,23,59,59,0,0) AS Result;
SELECT DATETIMEFROMPARTS (2010,12,31,23,59,59,0) AS Result;
SELECT DATETIMEOFFSETFROMPARTS (2010,12,31,14,23,23,0,12,0,7) AS Result;
SELECT SMALLDATETIMEFROMPARTS (2010,12,31,23,59) AS Result
SELECT TIMEFROMPARTS (23,59,59,0,0) AS Result;

Now let us see the result of the above script.

One of the interesting points to note is that in the DATETIME2FROMPARTS, the last parameter is the precision of datetime; it represents the fraction of the second. Here I would like to get back to you with a simple question,

“What is your opinion on these new functions and where exactly will you use them in real life?”

I am looking for creative answers, but each should be close to real-world usage.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – Denali – Date and Time Functions – DATEFROMPARTS() – DATETIMEFROMPARTS() – DATETIME2FROMPARTS() – TIMEFROMPARTS() – SMALLDATETIMEFROMPARTS() – DATETIMEOFFSETFROMPARTS() – A Quick Introduction

  1. I really cannot see the point of these and I think there might be a little straw clutching here whilst looking for new features. Most people would not use any of these and for those that do, it would only take a few minutes to knock together any of these functions in 2008.

    On area where this type of date splitting is used though is when using a date table. A date table is where you have specific date requirements, break downs and additional features. Its essentially a list of every day for a long period (many years) which additional information against each data such as special days (bank holidays, festivals etc.) plus columns for day, month etc. and can go as granular as hour. The date table is usually used in trend analysis (e.g. on sales data) so that sales can be mapped to it and changes in trend can be attributed to special events in the date table.

    The date table has many other uses but this is one where I could see this function being used, though it really isn’t hard without it.

    Like

  2. I guess it depends on the scenario, in cases where we have to put together a datetime column in a destination database and the source database is mainly providing them is parts, one could use these functions to formulate a datetime value.

    Like

  3. Pingback: SQL SERVER – Denali – 14 New Functions – A Quick Guide « Journey to SQLAuthority

  4. I have questions on date functions:
    1.Views and expressions that reference this column cannot be indexed.

    what does it mean?

    2.If i run the following script in Query Analyzer.

    SELECT GETDATE();

    It is giving results.
    2011-09-21 18:46:25.780

    can u tell me what is the last part 780 means?? — its changing frequently…!!!

    Like

  5. hi pinal,

    i guess… The faction of second (precision) available in DateTime2FromParts will be very useful in the scenario
    where lots of transactions happen in 1 second.

    e.g. stocks … If 30,000 transactions happen in 1 second, the this kind of Date function would be definately helpful :)

    – Khilit

    Like

  6. I’ve worked with many inherited systems (tend to be 3rd party applications!) whereby they store the day, month, year and hour, minute and seconds in separate columns. As stated above there are ways to concatenate these in current versions of SQL Server but i actually quite like the coding neatness of these.

    Like

  7. Hi Pinal,

    These functions would be useful in my current project where the energy data is stored in daily,hourly and interval datamarts.
    Ex:If i want to verify the data in hourly and daily marts i can just use this and compare the values.

    Thanks,
    Sreelekha

    Like

  8. These functions would prove useful in a reporting scenario (think SSRS) where you are prompting the user for a Month and Year, and want to do a BETWEEN.. like below:
    WHERE OrderDate BETWEEN DATEFROMPARTS(@STARTYEAR, @STARTMONTH, 1)
    AND EOMONTH(DATEFROMPARTS(@ENDYEAR, @ENDMONTH, 1))

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s