{"id":2575,"date":"2018-06-17T17:32:14","date_gmt":"2018-06-18T00:32:14","guid":{"rendered":"https:\/\/database.guide\/?p=2575"},"modified":"2021-01-28T14:11:24","modified_gmt":"2021-01-28T04:11:24","slug":"day-examples-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/day-examples-in-sql-server-t-sql\/","title":{"rendered":"DAY() Examples in SQL Server (T-SQL)"},"content":{"rendered":"<p>In <a href=\"https:\/\/database.guide\/what-is-sql-server\/\">SQL Server<\/a>,\u00a0 you can use the\u00a0<code>DAY()<\/code> function to return the &#8220;day&#8221; part of a date. This function returns an integer that represents the day of the month (not the day of the week).<\/p>\n<p>Below are examples of how to use this function.<\/p>\n<p><!--more--><\/p>\n<h2>Syntax<\/h2>\n<p>The syntax goes like this:<\/p>\n<pre>DAY ( date )<\/pre>\n<p>Where <code>date<\/code> is an expression that resolves to one of the following data types:<\/p>\n<ul>\n<li><strong>date<\/strong><\/li>\n<li><strong>datetime<\/strong><\/li>\n<li><strong>datetimeoffset<\/strong><\/li>\n<li><strong>datetime2<\/strong><\/li>\n<li><strong>smalldatetime<\/strong><\/li>\n<li><strong>time<\/strong><\/li>\n<\/ul>\n<p>This can be a column expression, expression, string literal, or user-defined variable.<\/p>\n<h2>Example<\/h2>\n<p>Here&#8217;s a basic example of how it works:<\/p>\n<pre>SELECT \r\n   SYSDATETIME() AS 'Date',\r\n   DAY(SYSDATETIME()) AS 'Day';<\/pre>\n<p>Result:<\/p>\n<pre>+-----------------------------+-------+\r\n| Date                        | Day   |\r\n|-----------------------------+-------|\r\n| 2018-06-18 00:20:22.1284540 | 18    |\r\n+-----------------------------+-------+<\/pre>\n<p>So the <code>DAY()<\/code> function was able to extract the day from the <strong>datetime2<\/strong> value (which was returned by the <a href=\"https:\/\/database.guide\/sysdatetime-examples-in-sql-server-t-sql\/\"><code>SYSDATETIME()<\/code><\/a> function).<\/p>\n<h2>Date Provided as a String Literal<\/h2>\n<p>Here&#8217;s an example where the date is provided as a string literal.<\/p>\n<pre>SELECT DAY('2019-01-07') AS Result;<\/pre>\n<p>Result:<\/p>\n<pre>+----------+\r\n| Result   |\r\n|----------|\r\n| 7        |\r\n+----------+<\/pre>\n<p>And here&#8217;s an example where the date is provided in a different format:<\/p>\n<pre>SELECT DAY('07\/01\/2017') AS Result;<\/pre>\n<p>Result:<\/p>\n<pre>+----------+\r\n| Result   |\r\n|----------|\r\n| 1        |\r\n+----------+<\/pre>\n<p>However, it&#8217;s usually best to avoid using dates in such formats. If you must do so, you&#8217;ll need to be mindful of the language settings and\/or the date format settings of the current session.<\/p>\n<h2>Language Settings<\/h2>\n<p>The output of the previous example will depend on the language settings and\/or date format settings of the current session.<\/p>\n<p>When we <a href=\"https:\/\/database.guide\/how-to-set-the-current-language-in-sql-server-tsql\/\">set the language<\/a>, the date format is implicitly set at the same time.<\/p>\n<p>Here&#8217;s what happens when we provide the same date argument in two different language environments.<\/p>\n<h3>British<\/h3>\n<pre>SET LANGUAGE British;\r\nSELECT DAY('07\/01\/2017') AS Result;<\/pre>\n<p>Result:<\/p>\n<pre>+----------+\r\n| Result   |\r\n|----------|\r\n| 7        |\r\n+----------+<\/pre>\n<h3>us_English<\/h3>\n<pre>SET LANGUAGE us_English;\r\nSELECT DAY('07\/01\/2017') AS Result;<\/pre>\n<p>Result:<\/p>\n<pre>+----------+\r\n| Result   |\r\n|----------|\r\n| 1        |\r\n+----------+<\/pre>\n<h2>Date Format Settings<\/h2>\n<p>The date format settings can override the language settings, so you also need to be aware of this setting. For example, we could be using <em>us_English<\/em> for our language (which has a default date format of <em>mdy<\/em>), but we could <a href=\"https:\/\/database.guide\/how-to-change-the-current-date-format-in-sql-server-t-sql\/\">override the date format<\/a> to be <em>dmy<\/em>.<\/p>\n<p>Here&#8217;s an example:<\/p>\n<h3>us_English &#8211; Default Date Format<\/h3>\n<p>Here, we set the language to <em>us_English<\/em>, which implicitly sets the date format to <em>myy<\/em>.<\/p>\n<pre>SET LANGUAGE us_English;\r\nSELECT DAY('07\/01\/2017') AS Result;<\/pre>\n<p>Result:<\/p>\n<pre>+----------+\r\n| Result   |\r\n|----------|\r\n| 1        |\r\n+----------+<\/pre>\n<h3>us_English &#8211; Override Date Format<\/h3>\n<p>Here, we set the language to <em>us_English<\/em>\u00a0(which implicitly sets the date format), but then we explicitly set the date format to <em>dmy<\/em>. This overrides the date format that was implicitly set when we set the language.<\/p>\n<pre>SET LANGUAGE us_English;\r\nSET DATEFORMAT dmy;\r\nSELECT DAY('07\/01\/2017') AS Result;<\/pre>\n<p>Result:<\/p>\n<pre>+----------+\r\n| Result   |\r\n|----------|\r\n| 7        |\r\n+----------+<\/pre>\n<h2>Return the Day Name<\/h2>\n<p>If you need to return the day name (as opposed to the day number), see <a href=\"https:\/\/database.guide\/3-ways-to-get-the-day-name-from-a-date-in-sql-server-t-sql\/\">3 Ways to Get the Day Name from a Date in SQL Server<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQL Server,\u00a0 you can use the\u00a0DAY() function to return the &#8220;day&#8221; part of a date. This function returns an integer that represents the day of the month (not the day of the week). Below are examples of how to use this function.<\/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,10,77,61],"class_list":["post-2575","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-date-functions","tag-dates","tag-functions","tag-how-to","tag-mssql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2575","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=2575"}],"version-history":[{"count":6,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2575\/revisions"}],"predecessor-version":[{"id":2584,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2575\/revisions\/2584"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2575"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}