loading..
Русский    English
03:55
go

DATEFROMPARTS function

DATEFROMPARTS function is available in SQL Server since 2012 version.

DATEFROMPARTS function has 3 integer parameters which represent year, month and day respectively. The function returns date corresponding to  these parameters. It is convenient way forming a date when its components are storing separately or transmitting from a client.

For example,

Console
Execute
  1. SELECT DATEFROMPARTS(2017,5,25);

returns
2017-05-25

To get the date corresponding to 25th day of current month you can use the following query

Console
Execute
  1. SELECT DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), 25);

Besides date, it is possible to construct values of TIME and DATETIME data types when using time components additionally to the date components. For example, the following query

Console
Execute
  1. SELECT TIMEFROMPARTS(9, 38, 59, 998, 7);
gives the value of TIME data type:
09:38:59.0000998

Pay attention to the last parameter which represents number of digits in the fractional part of a second. This integer parameter takes the value between 0 and 7 but can't be less than number of digits in a presentation of the fractional part of a second.

For example, the query

Console
Execute
  1. SELECT TIMEFROMPARTS(9, 38, 59, 998, 3);
returns
09:38:59.998
whereas the following

Console
Execute
  1. SELECT TIMEFROMPARTS(9, 38, 59, 998, 2);
gives the error:

Cannot construct data type time, some of the arguments have values which are not valid.

Compare with

Console
Execute
  1. SELECT TIMEFROMPARTS(9, 38, 59, 098, 2), TIMEFROMPARTS(9, 38, 59, 098, 3);

The result is
09:38:59.98   09:38:59.098

The DATETIMEFROMPARTS function constructs a value of DATETIME data type. It takes 7 integer parameters namely year, month, day, hours, minutes, seconds, and milliseconds respectively. In so doing, milliseconds component is rounded to an accuracy of one of the following values: .000, .003, and .007.

Console
Execute
  1. SELECT DATETIMEFROMPARTS(2017, 5, 13, 9, 38, 59, 998),
  2.  DATETIMEFROMPARTS(2017, 5, 13, 9, 38, 59, 999), 
  3. DATETIMEFROMPARTS(2017, 5, 13, 9, 38, 59, 993);

The above query gives the following
2017-05-13 09:38:59.997    2017-05-13 09:39:00.000    2017-05-13 09:38:59.993
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.