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

The GETDATE is one of the most popular built-in methods of  Microsoft SQL Server, but unlike its name suggest, 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 date like 2015-07-31, or just 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 date without time e.g. 2015-07-31, and a TIME data type to store time without any date information e.g. 15:42:54.470. Since GETDATE() function return 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 CONVERT function as shown in the last example of this article. I have also explained some difference 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 into older versions e.g. SQL Server 2004. See Microsoft SQL Server 2012 T-SQL Fundamentals to learn more about DATE, TIME and DATETIME data types.

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


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


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 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 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.  See Querying Microsoft SQL Server 2012 to learn more about 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:

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

Getting TIME without DATE from GETDATE in SQL Server

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

-- You can also use CAST which is 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 take 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 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 to prefer 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 specific date format then you have to use the CONVERT method because CAST doesn't provide date formatting.  You can further read Querying Microsoft SQL Server 2012 to learn more about the difference between CAST and CONVERT operators in SQL Server.

How to get DATE or TIME only from GETDATE() function in SQL Sever

Even though this book is part of Microsoft SQL Server example 70-461, it's still a worth reading if you are not preparing for the exam because it teaches you all 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 quick time.

That's all about how to retrieve DATE without time and TIME without date from GETDATE() function in SQL SERVER. You can use either CAST or CONVERT to get DATE and TIME value from DATETIME type in SQL Server, which apparently returns type of GETDATE function. If you are not converting the output of GETDATE to VARCHAR with specific date pattern, you should use 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)
  • How to increase the length of existing varchar column in SQL Server? (solution)
  • How to delete from table using join in SQL? (tutorial)
  • How to add columns on existing table in Microsoft SQL Server? (solution)
  • How to find 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)

SQL Shared Language Reference
Transact SQL Reference (Transact-SQL)

No comments :

Post a Comment