SQL SERVER 2012 – DateTime Functions – DATEFROMPARTS() – DATETIMEFROMPARTS() – DATETIME2FROMPARTS()

SQL
15 Comments

In SQL Server 2012, 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 an 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 of 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.

Solarwinds

SQL SERVER 2012 - DateTime Functions - DATEFROMPARTS() - DATETIMEFROMPARTS() - DATETIME2FROMPARTS() date2frompart

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 (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQLAuthority News – Implementing a Microsoft SQL Server Parallel Data Warehouse Using the Kimball Approach
Next Post
SQL SERVER – Denali – Date and Time Functions – EOMONTH() – A Quick Introduction

Related Posts

15 Comments. Leave new

  • the image is not displaying above..

    Reply
  • 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.

    Reply
  • Sports related? Formula one maybe?

    Reply
  • 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.

    Reply
  • Thanks for Sharing

    Reply
  • Srinivas Kadiyala
    September 21, 2011 10:55 pm

    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…!!!

    Reply
  • khilit.prajapati
    September 22, 2011 2:47 pm

    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

    Reply
  • where can i cross check (ie 2008/R2)

    Reply
  • The faction of second (precision) available in DateTime2FromParts will be very useful in Trading scenario

    Reply
  • 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.

    Reply
  • Sreelekha Vikram
    February 9, 2012 10:50 pm

    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

    Reply
  • 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))

    Reply
  • declare @tblTEMP01 table (
    Input varchar(20),
    JobDateTime datetime)

    INSERT INTO @tblTEMP01 (Input)
    SELECT CAST(last_run_date AS varchar) + RIGHT(‘000000’ + CAST(last_run_time AS varchar), 6)
    FROM sysjobsteps
    WHERE last_run_date ‘00000000’

    UPDATE @tblTEMP01
    SET JobDateTime = CAST(DATETIMEFROMPARTS(SUBSTRING(Input, 1, 4), SUBSTRING(Input, 5, 2), SUBSTRING(Input, 7, 2), SUBSTRING(Input, 9, 2),
    SUBSTRING(Input, 11, 2), SUBSTRING(Input, 13, 2), SUBSTRING(Input, 13, 2)) AS datetime)

    SELECT * FROM @tblTEMP01

    Reply
    • This is better :)

      declare @tblTEMP01 table (
      Input varchar(20),
      JobDateTime datetime)

      INSERT INTO @tblTEMP01 (Input)
      SELECT CAST(last_run_date AS varchar) + RIGHT(‘000000’ + CAST(last_run_time AS varchar), 6)
      FROM sysjobsteps
      WHERE last_run_date != ‘00000000’

      UPDATE @tblTEMP01
      SET JobDateTime = CAST(DATETIMEFROMPARTS(SUBSTRING(Input, 1, 4), SUBSTRING(Input, 5, 2), SUBSTRING(Input, 7, 2), SUBSTRING(Input, 9, 2),
      SUBSTRING(Input, 11, 2), SUBSTRING(Input, 13, 2), 0) AS datetime)

      SELECT * FROM @tblTEMP01

      Reply

Leave a Reply

Menu