GETDATE() ..Date and Time Functions in SQL Server 2008 R2

This blog will list different ways to use GETDATE() funtion in SQL Server 2008 R2The major changes in new SQL Server 2008 are the DateTime function and the introduction of new data types.

GETDATE()

 

So we all know the Function GETDATE()  returns the current system date and time.

Query:

select GETDATE()

output look like : 2017-02-27 10:53:49.737 

 

the output is only in numeric format.

----------------------------------------------------------------------------------------------------------------------------- 

if we want to convert into character format we can use the following query

 

Query:

select convert(char(20),getdate(),100)

output look like : Feb 27 2017 10:55AM 

here i convert the date to char format.

--------------------------------------------------------------------------------------------

we can use the above query in three format (above query is 1st format) for different use...

format 2:

Query:
select convert(char(20),getdate(),10)
output look like : 02-27-17
here ,if use '10' it returns only date from getdate() function


 
Note::

in the above query..

char(20) means- considering the total length of the output

if i try like ..

select convert(char(10),getdate(),100)
output look like : Feb 27 201    (here it took only 10 character length)
--------------------------------------------------------------------------------------------

Right() Function :

 

query : 

select right(convert(char(20),getdate(),100),8)

 

output look like: 12:33PM 

 

Here, the right() function consider only 8 characters from right to left 

 

for example ...

 

query : 

 

select right(convert(char(20),getdate(),100),6)

 

output look like: ':37PM '

 

here, in this query we use 6 (above query we use 8)..... that mean it consider 6 characters from right to left 

--------------------------------------------------------------------------------------------
left() Function :


same as right() function.. here it consider the characters from left side.. 
query : 
select left(convert(char(20),getdate(),100),8)
 
output look like: Feb 27 2 
 
Here, the left() function consider only 8 characters from left to right..