RSS

Remove Time portion from SQL server Date time object

27 May

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.

Advertisements
 
Leave a comment

Posted by on May 27, 2012 in MS SQL Server, SQL

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: