{"id":2546,"date":"2018-06-15T16:59:45","date_gmt":"2018-06-15T23:59:45","guid":{"rendered":"https:\/\/database.guide\/?p=2546"},"modified":"2021-01-28T14:11:26","modified_gmt":"2021-01-28T04:11:26","slug":"getdate-examples-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/getdate-examples-in-sql-server-t-sql\/","title":{"rendered":"GETDATE() Examples in SQL Server (T-SQL)"},"content":{"rendered":"

The GETDATE()<\/code> function returns the current date and time as a datetime<\/strong>\u00a0value. This value is derived from the operating system of the computer that the instance of SQL Server is running on.<\/p>\n

This article provides examples of the GETDATE()<\/code> function, including how you can use it with other functions to return the value you’re interested in.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

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

GETDATE ( )<\/pre>\n
So you simply call this function without any arguments.<\/p>\n
Example<\/h2>\n
Here’s a basic example of using a SELECT<\/code> statement to return the current date and time from GETDATE()<\/code>:<\/p>\n
SELECT GETDATE() AS Result;<\/pre>\n
Result:<\/p>\n
+-------------------------+\r\n| Result                  |\r\n|-------------------------|\r\n| 2018-06-15 23:54:42.013 |\r\n+-------------------------+<\/pre>\n
So as mentioned, it returns a datetime<\/strong> value. If you want a value with more seconds fractional precision, use SYSDATETIME()<\/code><\/a> instead. That function returns a datetime2<\/strong> value.<\/p>\n
Extract a Part of the Date<\/h2>\n
If you only want a part of the return value, you can use DATEPART()<\/code><\/a> to return only that part of the date\/time that you’re interested in.<\/p>\n
Example:<\/p>\n
SELECT DATEPART(month, GETDATE()) AS Result;<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 6        |\r\n+----------+<\/pre>\n
Sometimes there’s more than one way to get the same result in SQL Server. Here’s another example using the MONTH()<\/code> function:<\/p>\n
SELECT MONTH(GETDATE()) AS Result;<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 6        |\r\n+----------+<\/pre>\n
Both of those functions return the current month. But they return them as an integer representing the month number.<\/p>\n
If you want the month name<\/em> returned instead, you can use DATENAME()<\/code><\/a>:<\/p>\n
SELECT DATENAME(month, GETDATE()) AS Result;<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| June     |\r\n+----------+\r\n<\/pre>\n
 <\/p>\n
Format the Date<\/h2>\n
You can also use other T-SQL functions to format the date as required.<\/p>\n
Here’s an example of using the FORMAT()<\/code> function to format the result:<\/p>\n
SELECT \r\n    FORMAT(GETDATE(), 'd', 'en-US') AS 'd, en-US',\r\n    FORMAT(GETDATE(), 'd', 'en-gb') AS 'd, en-gb',\r\n    FORMAT(GETDATE(), 'D', 'en-US') AS 'D, en-US',\r\n    FORMAT(GETDATE(), 'D', 'en-gb') AS 'D, en-gb';<\/pre>\n
Result:<\/p>\n
+------------+------------+-----------------------+--------------+\r\n| d, en-US   | d, en-gb   | D, en-US              | D, en-gb     |\r\n|------------+------------+-----------------------+--------------|\r\n| 6\/15\/2018  | 15\/06\/2018 | Friday, June 15, 2018 | 15 June 2018 |\r\n+------------+------------+-----------------------+--------------+<\/pre>\n
More examples at How to Format the Date and Time in SQL Server<\/a>.<\/p>\n
Incrementing the Value and Finding the Difference<\/h2>\n
You can use functions like DATEDIFF()<\/code><\/a> to return the difference between the current date and another date.<\/p>\n
Here’s an example of using DATEADD()<\/code><\/a> to add a month to the current date, then finding out the difference in days:<\/p>\n
DECLARE @date1 datetime2 = GETDATE();\r\nDECLARE @date2 datetime2 = DATEADD(month, 1, GETDATE());\r\nSELECT DATEDIFF(day, @date1, @date2) AS Result;<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 30       |\r\n+----------+<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
The GETDATE() function returns the current date and time as a datetime\u00a0value. This value is derived from the operating system of the computer that the instance of SQL Server is running on. This article provides examples of the GETDATE() function, including how you can use it with other functions to return the value you’re interested … 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,10,77,61],"class_list":["post-2546","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\/2546","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=2546"}],"version-history":[{"count":3,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2546\/revisions"}],"predecessor-version":[{"id":2555,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2546\/revisions\/2555"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}