If you have done TSQL programming you might need declare a variable which will store a specific date. Lets say you want to store today date in a variable(which is apparently your server machine date assuming it’s correctly configured). This can be done with several ways here are some of the ways I used to do.
Method 1 – In this method what you does is you find number of Days between 1900-01-01 as reference date and which essentially truncates the time portion 🙂 and then you add that number of days to 1900-01-01 which truncates the date.
DECLARE @Today DateTime DECLARE @mydate datetime = GETDATE(); SELECT DATEADD(day, DATEDIFF(day, '19000101', @mydate), '19000101'); -- this will select today's date without time portion
Method 2 – in this method we convert datetime value to float which will give you a floating point representation of a datetime where in decimal portion representing the time portion so when you use floor function that floating point number rounds to the ground level integer which do the trick of removing the time portion and then you can convert that integer representation or day portion of the original datetime variable to datetime data type. Kind of clever method right? 🙂 Actually I prefer this method.
DECLARE @mydate DATETIME = GETDATE(); SELECT CAST( FLOOR(CAST(@mydate AS FLOAT)) AS DATETIME)
Method 3 – if you are using SQL server 2008 just use Date variable it will automatically removes time portion when you are storing date time values. 🙂
-- Only works on SQL Server 2008 and above DECLARE @mydate date = GETDATE(); SELECT @mydate
There are some other methods which use string manipulation to do the same task which I don’t like to use much often. I will research benchmark on these various methods and will blog about results to find the fastest most efficient method.