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.
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)
15 Comments. Leave new
the image is not displaying above..
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.
Sports related? Formula one maybe?
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.
Thanks for Sharing
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…!!!
It is a millisecond part of the time
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
where can i cross check (ie 2008/R2)
The faction of second (precision) available in DateTime2FromParts will be very useful in Trading scenario
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.
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
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))
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
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