SQL Server GETDATE () function and its use cases

This article will discuss an overview and use cases of the SQL Server GETDATE () function which is used to return the current date and time of the system on which SQL Server instance is running. There are several date-time related functions in SQL Server for distinct requirements like SYSDATETIME, CURRENT_TIMESTAMP, etc. All these functions will return the current date-time of the system on which the SQL Server is running. The only difference of having these many functions is the accuracy of the length of timestamp like till what precision you want to return your date time output. I will show you the output of some of these date-time functions and compare them with SQL Server GETDATE () function.

Below is the syntax of the GETDATE function. The output of this function will return in a ‘YYYY-MM-DD hh:mm:ss.mmm’ format.

1 2	SELECT GETDATE() GO

SQL Server GETDATE function is very flexible and can be used with various other date-time functions to return output in our desired format. Let’s first compare this function with other date-time functions available in SQL Server in the below section.

Compare SQL Server GETDATE () function with other date-time functions

As I have stated above, SQL Server offers multiple date and time functions to return current date time, date, or only time based on your requirement. If you want to return the date-time output in a specific format, then you can use any of these functions that are suitable to your requirement.

Have a look at the below example where I have fetched the current date-time output using various functions. You can compare them with each other to understand to what precision a specific function is returning its output.

SELECT GETDATE() AS [Getdate],

CURRENT_TIMESTAMP AS [Current Timestamp],

SYSDATETIME() AS [Sysdatetime]

Output is showing in the below image. All three functions are returning the same timestamp but SYSDATETIME is giving more fractional seconds accuracy as compared to the rest of the two functions.

We can see current timestamp returned by the above functions has a specific format. If we want to get the only current date of the system or only the time of the system, then we need to use another SQL Server function CONVERT to return only the date or time part of the above output. I will show this in the below use cases so that you can use them as per your requirement.

Run below T-SQL statements to return specific output like we want to get only the date portion of the above output. Here, I have executed all functions as a separate query, you can run it in a single query as well as I did in the above example.

SELECT CONVERT (Date, GETDATE()) AS [Current Date]

SELECT CONVERT (Date, CURRENT_TIMESTAMP) AS [Current Date]

SELECT CONVERT (Date, SYSDATETIME()) AS [Current Date]

Output is showing the current system date only for all system functions.

Similarly, we can get the current system time only by running the below queries. We just need to replace “Date” with “Time” in the bracket as shown in the below statements.

SELECT CONVERT (Time, GETDATE()) AS [Current Date]

SELECT CONVERT (Time, CURRENT_TIMESTAMP) AS [Current Date]

SELECT CONVERT (Time, SYSDATETIME()) AS [Current Date]

Here is the output where we can see the same time returned by all 3 functions except their fractional seconds.

If you want to get the same result, then you must execute all 3 functions in one query as I did in the first example. It captures the current time during the execution of their respective function and each statement was executed one after another that is why there is a tiny difference in a small part of their seconds.

Below is the execution of the same function under one query execution and we can see the time is the same for all 3 functions here because they executed under one batch.

Use cases of SQL Server GETDATE () function

The output of the above queries is returning in a specific format. Let’s assume you want to return the only year, month, date, or day then you cannot get that using the above queries. SQL Server offers few functions which we can use to get much deeper and granular level results using the SQL Server GETDATE function. These functions are DATENAME, DATEPART, DAY, MONTH, YEAR, etc.

Now, let’s discuss how to use the SQL Server GETDATE function with various other functions in the below section.

Use GETDATE function with DATENAME, DATEPART, and DATEADD functions

DATENAME and DATEPART are SQL Server functions that return the same information but in a different format. The DATENAME function will return the character string-based date and time of a specified date whereas the DATEPART function will return an integer-based date and time of a specified date.

DATEADD function helps us to get current, future, or past date-time based on the specified number to the input DateTime.

Below are the parts of date-time which can be returned using these functions:

Year
Quarter
Month
Day of year
Day
Week
Weekday
Hour
Minute
Second
Millisecond
Microsecond
Nanosecond
TZoffset
ISO_WEEK

Each date-time part has its abbreviation which can also be used to return the same output.

Here, I will use the SQL Server GETDATE function as a specified date for these functions to return various parts or portions of date and timestamp values.

SELECT DATENAME (YEAR, GETDATE()) AS [Year],

DATENAME (QUARTER, GETDATE()) AS [Quarter],

DATENAME (MONTH, GETDATE()) AS [Month],

DATENAME (DayOfYear, GETDATE()) AS [DayOfYear],

DATENAME (DAY, GETDATE()) AS [Day],

DATENAME (WEEK, GETDATE()) AS [Week],

DATENAME (WEEKDAY, GETDATE()) AS [Weekday],

DATENAME (HOUR, GETDATE()) AS [Hour],

DATENAME (MINUTE, GETDATE()) AS [Minute],

DATENAME (SECOND, GETDATE()) AS [Second],

DATENAME (MILLISECOND, GETDATE()) AS [Millisecond],

DATENAME (MICROSECOND, GETDATE()) AS [Microsecond],

DATENAME (NANOSECOND, GETDATE()) AS [NanoSecond],

DATENAME (ISO_WEEK, GETDATE()) AS [ISO Week]

We can also use its abbreviation to get its output like we can use mm or m for MONTHS, dd or d for DAY, etc.

Now we will run the above query with the DATEPART function and see the difference between their output. You can see, I have just replaced the DATENAME function with the DATEPART function in the below query.

SELECT DATEPART (YEAR, GETDATE()) AS [Year],

DATEPART (QUARTER, GETDATE()) AS [Quarter],

DATEPART (MONTH, GETDATE()) AS [Month],

DATEPART (DayOfYear, GETDATE()) AS [DayOfYear],

DATEPART (DAY, GETDATE()) AS [Day],

DATEPART (WEEK, GETDATE()) AS [Week],

DATEPART (WEEKDAY, GETDATE()) AS [Weekday],

DATEPART (HOUR, GETDATE()) AS [Hour],

DATEPART (MINUTE, GETDATE()) AS [Minute],

DATEPART (SECOND, GETDATE()) AS [Second],

DATEPART (MILLISECOND, GETDATE()) AS [Millisecond],

DATEPART (MICROSECOND, GETDATE()) AS [Microsecond],

DATEPART (NANOSECOND, GETDATE()) AS [NanoSecond],

DATEPART (ISO_WEEK, GETDATE()) AS [ISO Week]

Below is the output of the above query in which we can see all result sets have returned their value in integer datatype whereas results returned using the DATENAME function was in character strings.

The use of the DATEADD function is quite different. It helps us to get past or future dates based on the number expression passed to the specified value or parts of date and time as given in the below query.

SELECT GETDATE() AS [Today]

SELECT DATEADD (QUARTER, 2, GETDATE()) AS [Quarter],

DATEADD (MONTH, 2, GETDATE()) AS [Month],

DATEADD (DAY, 2, GETDATE()) AS [Day],

DATEADD (WEEK, 2, GETDATE()) AS [Week],

DATEADD (WEEKDAY, 2, GETDATE()) AS [Weekday]

I have used number 2 for each portion of date-time expressions so the above query will display:

2^nd quarter since today
2^nd month since today
2^nd day since today
2^nd week since today
2^nd weekday since today

Note, I have fetched the current date-time output using the SQL Server GETDATE function for noting what is today’s date and time. All calculations will happen from this value. You can pass any number to this query and output will return the nth value of that portion of date-time. If you want to get similar output from the past then you need to pass this number with negative signs like -2, -5, etc.

Compare each value and you can understand the output in a better way.

Use SQL Server GETDATE function with DAY, MONTH, EOMONTH, and YEAR functions

We can get such details about DAY, month, and year using the above functions as well but there is another way to get similar output like a portion of the day, month, and year.

SELECT YEAR(GETDATE()) AS [Year],

MONTH(GETDATE()) AS [Month],

DAY(GETDATE()) AS [Day],

EOMONTH(GETDATE()) AS [MonthEnd Date]

The above query will display the year, month, day, and last date of the current month.

We have used:

YEAR function to display current year from SQL Server GETDATE function
MONTH function to display current month number from SQL Server GETDATE function
DAY function to display date from SQL Server GETDATE function
EOMONTH function to display the last day of the current month with the help of the SQL Server GETDATE function

We can also use the EOMONTH function to display the last date of current, past, future, or any specified months. We need to pass a number in this function. That number will decide the nth month.

SELECT EOMONTH(GETDATE()) AS [Last Date of Current Month],

EOMONTH(GETDATE(), -1) AS [Last Date of Previous Month],

EOMONTH(GETDATE(), 1) AS [Last Date of Next Month]

You can analyze the below example where:

I have not passed any number to get the last date of the current month
I have used the -1 number to get the last date of the previous month
I have used 1 number to get the last date of next month

You can pass the number based on your requirement. If you want to get the last date of the 6^th month from today, then you need to pass 6 number to get its detail.

Have a look at the below output and analyze how this function is displaying the result of the last date of any month.

Convert date time format using CAST and CONVERT

This section will explain how to get the current date-time in various formats. Every region in this world uses different formats for date and time. Specific date-time formats are popular depending on distinct geolocations. We must consider and use a date-time format suitable to local users in our development.

SQL Server has offered two functions CAST and CONVERT to address date-time format issues. You can convert the current date as per your local standards using the CONVERT function. Here I will show you how to convert current date-time formats using CAST and CONVERT functions.

The below query will display the output of:

SQL Server GETDATE function
The next line of code has used function CAST to convert the existing current date-time format which is showing in the first column to another format MMM DD YYY HH:MM
CONVERT function will also return the same output as the CAST function

SELECT GETDATE() AS [Getdate],

CAST(GETDATE() AS NVARCHAR) AS [Getdate using CAST],

CONVERT(NVARCHARm GETDATE()) AS [Getdate using CONVERT]

I would recommend you to visit the attached MSDN link to get all applicable date and time formats in which we can get the current date and time using the SQL Server GETDATE () function with the help of the CONVERT function.

I have shown you some examples of different date-time formats using these functions. You can use them if they are suitable as per your requirement or you can visit the attached link and get the respective format number which will be used in the query to return date time in that specific format.

I have demonstrated the below example to display current date time in various formats starting from format number 0 to format no 7.

SELECT CONVERT(NVARCHARm GETDATE(), 0) AS [MMM DD YYYY HH:MM],

CONVERT(NVARCHARm GETDATE(), 1) AS [MMM/DD/YY],

CONVERT(NVARCHARm GETDATE(), 2) AS [YY.MM.DD],

CONVERT(NVARCHARm GETDATE(), 3) AS [DD/MM/YY],

CONVERT(NVARCHARm GETDATE(), 4) AS [DD.MM.YY],

CONVERT(NVARCHARm GETDATE(), 5) AS [DD-MM-YY],

CONVERT(NVARCHARm GETDATE(), 6) AS [DD MM YY],

CONVERT(NVARCHARm GETDATE(), 7) AS [MMM DD YY]

The output of the above query is showing in the below image in which you can see all these formats of current date-time.

There are tens of formats available in the MSDN link along with its defined format number which we will pass in the above query to get the current date-time as per our desired format.

Conclusion

I have explained an overview and its various use cases of SQL Server GETDATE function. This function is very useful to return the current timestamp in multiple formats depending on our requirement. You can understand it more by looking into several examples given in this article. You can also try other SQL Server date and time functions if the SQL Server GETDATE function is not suitable for your requirement. Explore more about these functions as well in the above sections where I have compared them with the GETDATE function.

Author
Recent Posts

Manvendra Singh

Manvendra is a database enthusiast, currently working as a Senior Architect at one of the top MNC.

He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.

You can also find him on LinkedIn

View all posts by Manvendra Singh

Latest posts by Manvendra Singh (see all)

Configure backup storage redundancy for Azure Cosmos DB account - May 15, 2024
Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
How to do a group by clause - April 12, 2023