Working with Date and Time in SQL Server

Introduction

Many developers use the DataTime function to find information related to date and time in SQL Server. In this article, I would like to show the similarity between the SQL GetDate function and the C# DateTime class. If you want to learn about DateTime in C#, read Working with DateTime in C#.

We use DateTime to work with dates, times, and both. The DateTime type in the C# language provides useful methods and properties for computing these values. So let's look at a practical example of how to use the SQL getdate function and C# DateTime with various queries to find the date and time.

DateTime

This represents an instant in time, typically a date and time of day. The DateTime class has two main properties to find the current date.

Today and Now property.

1. Today- This displays today's date. The time value is 12:00:00.

2. Now- This displays the current date and time of the system, expressed as the local time. In other words, the Now property returns a DateTime object with the current date and time values.

   Console.WriteLine("Today: {0}", DateTime.Today);
   Console.WriteLine("Today: {0}", DateTime.Now);

   Output

SQL Server Getdate Function

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

SELECT GETDATE() AS [DateTime]

Output

Current Date Without Time in C#

You can do it with the ToString function in C#. The following is a simple code for it.

string TodayDatewithouttime = DateTime.Now.ToString("dd/MM/yyy");
Console.WriteLine("Today Date without Time: {0}", TodayDatewithouttime);

Output

Current Date Without Time in SQL

The query below returns the date without time.

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

Output

Tomorrow Date Without Time in C#

You can do it with add days function in C#. The following is a simple code for it.

Console.WriteLine("Today: {0}", DateTime.Now);
string TomorrowDatewithouttime = DateTime.Now.AddDays(1).ToString("MM/dd/yyy");
Console.WriteLine("Tomorrow Date without Time: {0}", TomorrowDatewithouttime);

Output

Tomorrow Date Without Time in SQL

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 in C#

You can do it in C#. The following is a simple code for it.

Console.WriteLine("Today: {0}", DateTime.Now);
var today = DateTime.Now;
var month = new DateTime(today.Year, today.Month, 1);
var first = month.AddMonths(-1);
Console.WriteLine(" Start Date of Last Month : {0}", first.ToString("yyy/MM/dd"));  

Output

Start Date of Last Month in SQL

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

EndDate of Last Month in C#

You can do it in C#. The following is a simple code for it.

Console.WriteLine("Today: {0}", DateTime.Now);
var today = DateTime.Now;
var month = new DateTime(today.Year, today.Month, 1);          
var last = month.AddDays(-1);
Console.WriteLine(" End Date of Last Month : {0}", last.ToString("yyy/MM/dd"));

Output

End Date of Last Month in SQL

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

Output

Start Date of Current Month in C#

You can do it in C#. The following is a simple code for it.

Console.WriteLine("Today: {0}", DateTime.Now);
var today = DateTime.Now;
var StartDate = new DateTime(today.Year, today.Month, 1);          
Console.WriteLine("Start Date of current Month : {0}", StartDate.ToString("yyy/MM/dd"));

Output

Start Date of Current Month in SQL

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

 

Conclusion

This article taught us about working with date and time with different types and examples in SQL Server and C#.