Shubham
Shubham

Reputation: 39

Cast vs Convert SQL Server

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions