Remove TIME from a DATETIME

In This Topic

To remove the time part from a datetime you can do it the following ways in both SQL Server and VistaDB.

Sql Example	Copy Code
-- SET SOME DATE WITHOUT A TIME (doesn't matter what it is) declare @somedateonly datetime; set @somedateonly = '2000-01-01 00:00:00'; SELECT DATEADD(dd, DATEDIFF(dd, @somedateonly, GETDATE() ), @somedateonly );

Sql Example

Copy Code

-- SET SOME DATE WITHOUT A TIME (doesn't matter what it is) 
declare @somedateonly datetime; 
set @somedateonly = '2000-01-01 00:00:00'; 
SELECT DATEADD(dd, DATEDIFF(dd, @somedateonly, GETDATE() ), @somedateonly );

You could also do it in a single statement like this:

SELECT DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', GETDATE() ), '2000-01-01 00:00:00' );

In SQL Server you could just pass 0 as the datetime. In .Net a 0 does not cast to a DateTime.

.NET Example	Copy Code
DateTime targetdateonly = (DateTime)0; // Does not work DateTime targetdateonly = Convert.ToDateTime(0); // Does work

DateTime targetdateonly = (DateTime)0; // Does not work DateTime targetdateonly = Convert.ToDateTime(0); // Does work

How does this work?

The calls are really subtracting the number of days from the GETDATE() call and the entered date. Then it adds the difference to the entered date (which contained no time). The end result is that you have the date without a time being set.