Getting Some Useful Date and Time Information From Getdate Function in SQL Server 2012

In this article, I would like to show the utility of Getdate functions in SQL Server. So let's have a look at a practical example of how to use the getdate function with a different query to find the date and time in SQL Server 2012. The example is developed in SQL Server 2012 using SQL Server Management Studio.

Getdate Function

The GETDATE() function returns the current date and time from the SQL Server.

SELECT GETDATE() AS [DateTime]

Output

Curent Date Without Time

The query below returns the date without time.

Declare @date datetime

set @date=DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)

select @date

Output

Tomorrow's Date Without Time

The query below returns Tomorrow's date without time.

Declare @date date

set @date=DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1)

select @date

Output

Start Date of Last Month

The query below returns the start date of the previous month.

DECLARE @StartDateofLastMonth DATETIME

SET @StartDateofLastMonth = DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0)

select @StartDateofLastMonth

Output

End Date of Last Month

The query below returns the end date of the previous month.

DECLARE @StartDateofLastMonth DATETIME, @EndDateofLastMonth DATETIME

SET @StartDateofLastMonth = DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0)

SET @EndDateofLastMonth = dateadd(dd, -1, DATEADD(mm, 1, @StartDateofLastMonth))

select @EndDateofLastMonth

Start Date of Current Month

The query below returns the start date of the current month.

DECLARE @StartDateofLastMonth DATETIME
SET @StartDateofLastMonth = DATEADD(month, datediff(month, 0, getdate()), 0)

Select @StartDateofLastMonth

Output