Cast vs Convert SQL Server
I am constantly working with datetime columns in SQL Server. Now, most of the time, I have to reset some of the datetime to '00: 00: 00.000 '.
I am using the cast function to achieve the same:
select cast(cast(getdate() as date)as datetime)
Now some other members of my team are using different features:
select cast(floor(cast(GETDATE() as float))as datetime)
OR
SELECT CONVERT(VARCHAR,GETDATE(),105)
Which function should you use given that the index column is a date type column. (Hence, I am converting datetime -> date -> datetime using two times).
source to share
There are good reasons why you shouldn't do the second and third options. Consider this first:
select cast(floor(cast(GETDATE() as float)) as datetime)
My problem is that while it works, I cannot find any documentation that defines the behavior. The internal format is not a floating point number; these are two integers, so I find it rather inelegant to go from date / time to float. There may be situations in which it is dangerous.
Further:
SELECT CONVERT(VARCHAR,GETDATE(),105)
This version., Arggh! It has varchar()
no length argument. Bad habit. Do not do this! This is a confusing problem waiting to happen. So, consider:
SELECT CONVERT(VARCHAR(10), GETDATE(), 105)
It's ok if you want a string, but the result is different from your other queries. Equivalent instruction:
SELECT CONVERT(DATE, CONVERT(VARCHAR(10), GETDATE(), 105), 105)
But wait, why go through the intermediate string structure? The version you suggest is simpler.
The only drawback is that it is incompatible with versions of SQL Server prior to 2008. If you need to be backward compatible, I would go to yucky:
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
This counts the number of days since the dummy date "0" and then adds them back in. If you don't mind typing, the following looks something like this:
SELECT DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE()), '1900-01-01')
or pass an intermediate string value.
The reason SQL Server has multiple workarounds for this feature is because the functionality is very useful, but the date type date
was only introduced in SQL Server 2008.
source to share