tushkieeeee (9/1/2010)
please try the code below. I think this is what the auhtor wanted to convey.
I don't think so. I think the author wanted to convey that implicit conversion from datetime to int is not allowed. In this discussion, the examples somehow switched to implicit conversion from int to datetime, which is fine.
Saurabh Dwivedy (9/1/2010)Is this dependent on the sql server version?
I think the above also answers this - as far as I know, the behaviour is the same in all SQL Server versions (though not when using the new date and time datatypes instead of datetime!): implicit conversion is always allowed from int to datetime, and never from datetime to int.
mbova407 (9/1/2010)
This is how we get the date for the current day without time element(...)
the -.5 is half a day because CONVERT(INT,@b) rounds
I don't really like this method - the addition of an integer value to a datetime, though documented, is wacky, and it doesn't work anymore when switching to the new date or time datatypes; the addition of a non-integer to a datetime is (as far as I know) not even documented.
Dave62 (9/1/2010)
Looks like the time is still there but just 0.Here's another way to get the current date without the time:
Select CONVERT(varChar(10), getDate(), 101);This returns: 09/01/2010
As long as you keep the datetime datatype, you'll always have a time part. But it can be very useful to ensure the time part is 0, if you need only the day part - it makes comparisons a lot easier. When running SQL2008, you can of course use date instead, but many people are still running SQL2005.
Your alternative is fine if you need the date in character format for presentation purposes. When using it for calculations, you'd have to convert back to datetime. That would at least require you to use a locale-neutrall format (09/01/2010 is the ninth of january in most of the world!), but then you'd still be taking the performance hit of two expensive converstions (datetime to char and char to datetime). Here is my favorite way to force the time part of a datetime to midnight:
SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');
For much more information, see http://www.karaszi.com/SQLServer/info_datetime.asp