{"id":2528,"date":"2018-06-14T16:00:01","date_gmt":"2018-06-14T23:00:01","guid":{"rendered":"https:\/\/database.guide\/?p=2528"},"modified":"2021-01-28T14:11:27","modified_gmt":"2021-01-28T04:11:27","slug":"datetimeoffsetfromparts-examples-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/datetimeoffsetfromparts-examples-in-sql-server-t-sql\/","title":{"rendered":"DATETIMEOFFSETFROMPARTS() Examples in SQL Server (T-SQL)"},"content":{"rendered":"<p>T-SQL has a function called <code>DATETIMEOFFSETFROMPARTS()<\/code> that allows you to get a <strong>datetimeoffset<\/strong>\u00a0value from the various separate parts of a date. Specifically, it returns a\u00a0<strong>datetimeoffset<\/strong>\u00a0value for the specified date and time and with the specified offsets and precision.<\/p>\n<p>Examples of this function below.<\/p>\n<p><!--more--><\/p>\n<h2>Syntax<\/h2>\n<p>The syntax goes like this:<\/p>\n<pre>DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )<\/pre>\n<p>Where:<\/p>\n<ul>\n<li>The first 7 arguments are integer expressions specifying that particular date\/time part.<\/li>\n<li>The <code>hour_offset<\/code> argument is an integer expression specifying the hour portion of the time zone offset.<\/li>\n<li>The <code>minute_offset<\/code> is an integer expression specifying the minute portion of the time zone offset.<\/li>\n<li>The <code>precision<\/code> argument is an integer literal specifying the precision of the\u00a0<strong>datetimeoffset<\/strong>\u00a0value to be returned. This value actually specifies the scale (i.e. the number of digits to the right of the decimal place).<\/li>\n<\/ul>\n<h2>Example<\/h2>\n<p>Here&#8217;s an example of usage.<\/p>\n<pre>SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10, 23, 35, 29, 500, 12, 30, 4 ) \r\nAS Result;<\/pre>\n<p>Result:<\/p>\n<pre>Result\r\n----------------------------------\r\n2021-05-10 23:35:29.0500 +12:30<\/pre>\n<h2>Invalid Arguments<\/h2>\n<p>You need to ensure that all arguments are valid, otherwise you&#8217;ll get an error. Here&#8217;s an example of providing an out of range hour part (25). The hour part can only be between 0 and 24.<\/p>\n<pre>SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10, 25, 35, 29, 500, 12, 30, 4 ) \r\nAS Result;<\/pre>\n<p>Result:<\/p>\n<pre>Cannot construct data type datetimeoffset, some of the arguments have values which are not valid.<\/pre>\n<h2>Number of Arguments<\/h2>\n<p>You need to provide the correct number of arguments (10). If you don&#8217;t you&#8217;ll get an error.<\/p>\n<pre>SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10 ) AS Result;<\/pre>\n<p>Result:<\/p>\n<pre>The datetimeoffsetfromparts function requires 10 argument(s).<\/pre>\n<h2>Null Arguments<\/h2>\n<p>If any of the first 9 arguments are null, the result is <code>NULL<\/code>:<\/p>\n<pre>SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10, NULL, 35, 29, 500, 12, 30, 4 ) \r\nAS Result;<\/pre>\n<p>Result:<\/p>\n<pre>Result\r\n----------------------------------\r\nNULL<\/pre>\n<p>However, if the last (precision) argument is null, then an error is returned:<\/p>\n<pre>SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10, 23, 35, 29, 500, 12, 30, NULL ) \r\nAS Result;<\/pre>\n<p>Result:<\/p>\n<pre>Scale argument is not valid. Valid expressions for data type datetimeoffset scale argument are integer constants and integer constant expressions.<\/pre>\n<p>Also see\u00a0<a href=\"https:\/\/database.guide\/datetime2fromparts-examples-in-sql-server-t-sql\/\">DATETIME2FROMPARTS() Examples in SQL Server (T-SQL)<\/a> for returning a <strong>datetime2<\/strong> value (without the offset).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>T-SQL has a function called DATETIMEOFFSETFROMPARTS() that allows you to get a datetimeoffset\u00a0value from the various separate parts of a date. Specifically, it returns a\u00a0datetimeoffset\u00a0value for the specified date and time and with the specified offsets and precision. Examples of this function below.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[68,115,93,77,61],"class_list":["post-2528","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-date-functions","tag-dates","tag-functions","tag-mssql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2528","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2528"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2528\/revisions"}],"predecessor-version":[{"id":4483,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2528\/revisions\/4483"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2528"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2528"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2528"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}