In this blog, I'm going give you different methods to use the GETDATE() function in SQL.
Basically we all know the behavior of GETDATE(). By calling this function we can get the current date and time of the computer. But it returns not only date; it also returns time. If your requirement is date alone or time alone, then how can we get that? Let's see,
Method 1- GETDATE()
So we all know the basic Functionality of GETDATE(), it returns the current date and time of the system.
Query- select GETDATE()
Output - 2017-02-27 10-53-49.737
Note- the output of this function is in numeric format.
Method 2- Convert() function
If we want the date and time in character format, by using convert() function we can get the output in character format like below,
Query- select convert(char(20),getdate(),100)
Output - Feb 27 2017 10-55AM
Note- Here I used ‘100’ inside of convert function because, if we use 100 it returns both date and time.
Method 3- Convert() function for Date
If we want only date from the GETDATE() function by using the same convert function with the parameter of ’10’.
Query- select convert(char(20),getdate(),10)
Output - 02-27-17
Method 4- Convert() function for Time
If we want only time alone from the GETDATE() function by using the same convert function with the parameter of ’108’.
Query: select convert(char(20),getdate(),108)
output - 12-32-17
Note - in the above query we used char length is 20, which means considering the total length of the output.
Method 5- Convert() function with different character length
Even by adjusting the character length we can get our expected output from the query, like below,
By using the below query we can get only month and current date as output from GETDATE() function
Query - select convert(char(6),getdate(),100)
Output - Feb 27
Note- I got this output by changing the length of the character.
Method 6- Right() Function
By using Right() function in the below query we can get only current time and session as output from GETDATE() function
Query - select right(convert(char(20),getdate(),100),8)
Output - 12-33PM
Note- Here, we used right() function with ‘8’ as parameter. Which represents only 8 characters from right to left of the query output.
For example,
Query - select right(convert(char(20),getdate(),100),6)
Output - '-37PM '
Note- Here, we used the character length as 6 (in the above query we use 8) , which means it considers only 6 characters from right to left
Likewise, instead of character output if we need numeric output of only hours, minutes and seconds, so we write the query without the convert function like below,
Query - select right(getdate(),12)
Output - 12-33-11.737
Method 7- Left() Function
Same Like right() function, By using Left() function we can do left side operation like below
Query - select left(convert(char(20),getdate(),100),8)
Output - Feb 27 2
Note- Here, we used left() function with the length of ‘8’, so it consider only 8 characters from left to right.