How to get just DATE or TIME from GETDATE() in SQL Sever - Example Tutorial

The GETDATE is one of the most popular built-in methods of  Microsoft SQL Server, but unlike its name suggests, it doesn't return just date, instead, it returns date with time information e.g. 2015-07-31 15:42:54.470, quite similar to our own java.util.Date from Java world. If you want just a date like 2015-07-31, or just a time like 15:42:54.470 then you need to either CAST or CONVERT output of GETDATE function into DATE or TIME data type. From SQL Server 2008 onward, apart from DATETIME, which is used to store both date and time, You also have a DATE data type to store data without time e.g. 2015-07-31, and a TIME data type to store time without any date information like 15:42:54.470.


Since the GETDATE() function returns a DATETIME value, You have to use either CAST or CONVERT method to convert a DATETIME value to DATE or TIME in SQL Server.

If you are looking for a VARCHAR value, formatted into a specific format e.g. YYYYMMDD then you can also convert the DATETIME value to VARCHAR with a pattern using the CONVERT function as shown in the last example of this article. I have also explained some differences between CAST and CONVERT methods and which one you should prefer.


Remember, DATE and TIME types are only available from Microsoft SQL Server version 2008 onward e.g. SQL Server 2012 or 2014, but you won't find them in older versions e.g. SQL Server 2004.

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join a comprehensive course to learn more about DATE, TIME, and DATETIME data types. If you need a recommendation then I suggest you go through the Microsoft SQL for Beginners online course by Brewster Knowlton on Udemy. It's a great course to start with T-SQL and SQL queries in SQL Server.

How to get DATE from GETDATE function in SQL SERVER

When you can call the GETDATE() function as SELECT GETDATE() it returns a DATETIME value. In order to retrieve just get DATE, we need to cast output of GETDATE() into DATE data type as shown below :

SELECT GETDATE() -- 2015-07-31 15:42:54.470

SELECT CAST (GETDATE() AS DATE) -- 2015-07-31

You can also use CONVERT() to get just DATE without time from the GETDATE() function as shown below :

SELECT CONVERT(DATE, GETDATE()) -- 2015-07-31

A couple of worth noting points about DATE, TIME, and DATETIME data types in SQL Server:

• From SQL Server 2008 onwards we have both DATE and TIME data types
• A DATE is just a date without time e.g. 2015-07-31, similar to LocalDate of Java 8
• A TIME is just time without date e.g. 15:42:54:470 , similar to LocalTime of Java 8

Now, many of you will be thinking about whether to use CAST or CONVERT for converting DATETIME into DATE and TIME data types? Well, you can use any of them but  CAST is also SQL ANSI standard, it's better to use CAST than CONVERT.  

You can further see these databases and SQL courses to learn more about the cast and convert and dealing with date and time in MSSQL. One of the must-read books for any SQL Server developer.

Here is also a nice slide about various date and time data types in SQL Server, available from MSSQL 2008 onwards:

Getting TIME without DATE from GETDATE in SQL Server

SELECT CONVERT(TIME, GETDATE()) -- 15:43:20.4770000

-- You can also use CAST which is a standard way to convert one date type to another in Microsoft SQL Server

SELECT CAST (GETDATE() AS TIME) -- 15:47:54.6730000

-- If you want Date as formatted String into VARCHAR variable then CONVERT function also supports that i.e. it takes GETDATE output and return VARCHAR in a specific date format. To achieve that you should use CONVERT with date format  e.g. 101 to display the date as MM/dd/yyyy format as shown in the following example:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) -- 07/31/2015

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) -- 20150731

Even though I suggested preferring CAST over CONVERT there are situations where you can only use the CONVERT e.g. if you want a formatted date e.g. Date in a specific date format then you have to use the CONVERT method because CAST doesn't provide date formatting.  You can further read these free SQL Server books to learn more about the difference between CAST and CONVERT operators in SQL Server.

Even though this book is part of Microsoft SQL Server example 70-461, it's still worth reading if you are not preparing for the exam because it teaches you all the important details about SQL Server. I have read a couple of books on SQL Server and this is one of the best to learn SQL Server in a quick time.


That's all about how to retrieve DATE without time and TIME without date from the GETDATE() function in SQL SERVER. You can use either CAST or CONVERT to get DATE and TIME value from the DATETIME type in SQL Server, which apparently returns the type of GETDATE function. 

If you are not converting the output of GETDATE to VARCHAR with a specific date pattern, you should use the CAST method because it's also compliant with SQL standard and more likely work in other SQL-compliant databases like Oracle or MySQL.


Other SQL Server tutorials You may like

• How to replace null with empty String in SQL Server? (solution)
• 50 SQL Server Phone Interview Questions (answers)
• How to increase the length of the existing varchar column in SQL Server? (solution)
• How to delete from a table using join in SQL? (tutorial)
• How to add columns to the existing tables in Microsoft SQL Server? (solution)
• How to find the length of String in MSSQL? (solution)
• Difference between row_number(), rank(), and dense_rank() in SQL? (answer)
• SQL query to find all table names in a database? (query)
• Difference between SQL queries in Oracle and Microsoft SQL Server? (answer)
• 50 Database and SQL Interview Questions (answers)
• Difference between PL/SQL and T-SQL (answer)

Thanks a lot for reading this SQL Server tutorial so far. If you like this tutorial about how to get DATE and TIME from GET and my example then please share them with your friends and colleagues. If you have any questions, please drop a note. 

P. S. - If you are new to SQL Server and T-SQL and looking for free resources to learn the basics of SQL Server and Transact-SQL or T-SQL then you can also check out these free T-SQL courses from Udemy. It's a great resource to start with SQL Server and T-SQL.