{"id":2515,"date":"2018-06-13T16:18:41","date_gmt":"2018-06-13T23:18:41","guid":{"rendered":"https:\/\/database.guide\/?p=2515"},"modified":"2021-01-28T14:11:28","modified_gmt":"2021-01-28T04:11:28","slug":"datefromparts-examples-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/datefromparts-examples-in-sql-server-t-sql\/","title":{"rendered":"DATEFROMPARTS() Examples in SQL Server (T-SQL)"},"content":{"rendered":"

You can use the T-SQL<\/a> DATEFROMPARTS()<\/code> function in SQL Server<\/a> to return a date<\/strong> value that maps to the date parts that you provide as integer expressions.<\/p>\n

The way it works is, you provide three values (one for each of the year<\/em>, month<\/em>, and day<\/em> values), and SQL Server will return a date<\/strong> value based on the values you provide.<\/p>\n

Examples below.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

First, here’s the syntax:<\/p>\n

DATEFROMPARTS ( year, month, day )<\/pre>\n
Here’s what each argument can be:<\/p>\n
\n
year<\/code><\/dt>\n
An integer expression that specifies a year.<\/dd>\n
month<\/code><\/dt>\n
An integer expression that specifies a month, from 1 to 12.<\/dd>\n
day<\/code><\/dt>\n
An integer expression that specifies a day.<\/dd>\n<\/dl>\n
Example<\/h2>\n
Here’s an example of usage:<\/p>\n
SELECT DATEFROMPARTS( 2021, 05, 10 ) AS Result;<\/pre>\n
Result:<\/p>\n
+------------+\r\n| Result     |\r\n|------------|\r\n| 2021-05-10 |\r\n+------------+\r\n<\/pre>\n
Invalid Values<\/h2>\n
You’ll need to ensure that the date parts are provided in the correct order, otherwise you could end up with an error. Worse yet, you might not get an error (if the day portion is 12 or less), and therefore could miss a major problem with the way the date is translated.<\/p>\n
Here’s an example of an invalid value:<\/p>\n
SELECT DATEFROMPARTS( 2021, 30, 10 ) AS Result;<\/pre>\n
Result:<\/p>\n
Cannot construct data type date, some of the arguments have values which are not valid. \r\n<\/pre>\n
This occurs because we provided 30 as the month part. This part will only accept values between 1 and 12.<\/p>\n
Null Values<\/h2>\n
If any of the arguments are null values, the result is NULL<\/code>.<\/p>\n
Here’s an example using a null value:<\/p>\n
SELECT DATEFROMPARTS( 2021, NULL, 10 ) AS Result;<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| NULL     |\r\n+----------+\r\n<\/pre>\n
Therefore, it probably goes without saying (but I’ll say it anyway), that if all arguments are null values, the result is NULL<\/code>:<\/p>\n
SELECT DATEFROMPARTS( NULL, NULL, NULL ) AS Result;<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| NULL     |\r\n+----------+\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
You can use the T-SQL DATEFROMPARTS() function in SQL Server to return a date value that maps to the date parts that you provide as integer expressions. The way it works is, you provide three values (one for each of the year, month, and day values), and SQL Server will return a date value based … Read more<\/a><\/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-2515","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\/2515","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=2515"}],"version-history":[{"count":3,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2515\/revisions"}],"predecessor-version":[{"id":2518,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2515\/revisions\/2518"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2515"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2515"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2515"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}