3

I am constantly working with datetime columns in SQL Server. Now most of the time I have to reset the time portion of the datetime to '00:00:00.000'.

I use the cast function to achieve the same: 

select cast(cast(getdate() as date)as datetime)

Now some other of my team members use other functions:

select cast(floor(cast(GETDATE() as float))as datetime)

OR

SELECT CONVERT(VARCHAR,GETDATE(),105)

Which function should I go for keeping in mind that the index column is a datetime type column. (Hence I convert the datetime -> date -> datetime using cast twice).

Mike G
  • 4,232
  • 9
  • 40
  • 66
Shubham
  • 39
  • 1
  • 6
  • 1
    possible duplicate of [Floor a date in SQL server](http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server) – Hogan May 22 '15 at 17:16
  • 1
    TLDR; `select dateadd(day,datediff(day,0,@datetime),0);` – Hogan May 22 '15 at 17:17

1 Answers1

7

There are good reasons why you should not do the second and third options. First consider this:

select cast(floor(cast(GETDATE() as float)) as datetime)

My problem with this is that, although it does work, I cannot find documentation that specifies the behavior. The internal format is not a floating point number; it is two integers, so I find it quite inelegant to go from a date/time to a float. There might exist situations where it is dangerous.

Next:

SELECT CONVERT(VARCHAR,GETDATE(),105)

This version . . . Arggh! It has varchar() with no length argument. Bad habit. Don't do that! It is a confusing problem waiting to happen. So, let's consider:

SELECT CONVERT(VARCHAR(10), GETDATE(), 105)

This is fine if you want a string, but the result is different from your other queries. The equivalent statement is:

SELECT CONVERT(DATE, CONVERT(VARCHAR(10), GETDATE(), 105), 105)

But wait, why go through the intermediate structure of a string? The version you first propose is simpler.

The only downside is that it is not compatible with pre-2008 versions of SQL Server. If you need to be compatible with earlier versions, then I would go for the yucky:

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

This counts the number of days since a fictitious "0" date and then adds them back. If you don't mind typing, the following is pretty much the same thing:

SELECT DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE()), '1900-01-01')

or go through an intermediate string value.

The reason SQL Server has several work-arounds for this functionality is because the functionality is very useful, but the date date type was only introduced in SQL Server 2008.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon... For taking time and giving a detailed explanation... :) Really appreciate it !!! – Shubham May 26 '15 at 09:14