Reputation: 39
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).
Upvotes: 3
Views: 2273
Reputation: 1269873
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.
Upvotes: 7